How to do Xlookup between worksheets?

[KAKI]

I am trying to look up values in another worksheet where it contains all the information.

As a test, I have typed in the same formula as shown in the Vectorworks example, however nothing is happening...

 

 

Function example (copied from vectorworks)

=XLookup(value, [use_pattern], not_found_value, array_lookup, array_result)

Find a value in the array_lookup, and return the value from the array_result at the found row. the arrays should be ranges on the same column.

Parameters:
  value - the value that will be searched in 'array_lookup' (a range defined in a single column).
  use_pattern - use regular expression pattern for the search.
  not_found_value - the result if 'value' is not found
  array_lookup - a range defined in a single column to search the 'value' in
  array_result - a range defined in a single column to to provide the result from the row that the 'value' was found in 'array_lookup'

=XLookup('tech', 'not found', 'Worksheet-Values':A1..A3, 'Worksheet-Result':B1..B3)

Named worksheet: 'Worksheet-Values':

  |     A     
--------------
1 |  support
2 |  tech
3 |  PR       

Named worksheet: 'Worksheet-Result':

  |   A    |     B
-----------|-------------------------
1 |  1234  | support@vectorworks.net
2 |  1235  | tech@vectorworks.net
3 |  1236  | PR@vectorworks.net

 

 

 

 

[Pat Stanford]

=XLookup(value, [use_pattern], not_found_value, array_lookup, array_result)

=XLookup('tech', 'not found', 'Worksheet-Values':A1..A3, 'Worksheet-Result':B1..B3)

The above is from the documentation on worksheet functions on the developer site.

 

https://developer.vectorworks.net/index.php?title=Worksheet_Functions

[KAKI]

18 hours ago, Pat Stanford said:

=XLookup(value, [use_pattern], not_found_value, array_lookup, array_result)

=XLookup('tech', 'not found', 'Worksheet-Values':A1..A3, 'Worksheet-Result':B1..B3)

The above is from the documentation on worksheet functions on the developer site.

 

https://developer.vectorworks.net/index.php?title=Worksheet_Functions

 

This is what I've typed and nothing happened, I figured out I had to use a semi-colon.

But when I tried to look up a whole column, again nothing is working. Is there a way to look up a whole column instead of using A1..A100? Because when I copy and paste the formula to the rows below, it changes to A2 - A101 and so on. Then other entries in the column will be missed out.

Seems like the xlookup function in the vectorworks doesn't work as good as the excel one.

 

Also, is there a way to look up the symbols in the worksheet column? When I was trying with this formula my Vectorworks just crashed.

 

 

 

[Pat Stanford]

Use a dollar sign in front of the row to force an absolute reference instead of a relative reference.

 

A$2..A$100