# XLOOKUP function

**XLOOKUP**(SoughtValue, LookupArray, ResultArray, FallbackValue?, MatchMode?, SearchMode?)

**XLOOKUP**(SoughtValue; LookupArray; ResultArray; FallbackValue?; MatchMode?; SearchMode?)

## SoughtValue

The value to look for in the lookup array.

## LookupArray

The array which may or may not contain the sought value. This array
must be identical in size to the `resultArray`

parameter.

## ResultArray

The array containing results. If the sought value can be found in the
lookup array, the element from the result array is returned whose
position matches that of the found element. This array must be
identical in size to the `lookupArray`

parameter.

## FallbackValue

The value to return if the sought value cannot be found in the lookup
array. If omitted, an `#N/A`

error is used. To create an
`#N/A`

error for use with this parameter, use the NA function.

## MatchMode

0 for an exact match, -1 for an exact match or the next smallest value, 1 for an exact match or the next largest value or 2 for a wildcard text string match. If omitted, 0 is assumed, meaning that an exact match is needed. 0 can also be written as MatchMode.ExactMatchMode,Exact, -1 can be written as MatchMode.ExactOrNextSmallestMatchMode,ExactOrNextSmallest, 1 can be written as MatchMode.ExactOrNextLargestMatchMode,ExactOrNextLargest and 2 can be written as MatchMode.WildcardMatchMode,Wildcard.

## SearchMode

1 to search the lookup array starting with the first array element and ending with the last element, -1 to search starting with the last array element and ending with the first element, 2 for a binary search where the lookup array is ordered in ascending order and -2 for a binary search where the lookup array is ordered in descending order. If omitted, 1 is assumed, meaning that the lookup array is searched starting with the first array element and ending with the last element. 1 can also be written as SearchMode.FirstToLastSearchMode,FirstToLast, -1 can be written as SearchMode.LastToFirstSearchMode,LastToFirst, 2 can be written as SearchMode.BinarySearchAscendingSearchMode,BinarySearchAscending and -2 can be written as SearchMode.BinarySearchDescendingSearchMode,BinarySearchDescending.

## Returns

The array element in the result array with the same position as an
element in the lookup array which is equal to a given value. If the
value cannot be found, the given fallback value is returned instead. If
there is no fallback value, an `#N/A`

error is returned.

Looks up a value in the second array which corresponds to a value in the
first array matching the first parameter. XLOOKUP(FullName, Employees!Name,
Employees!Salary)XLOOKUP(FullName; Employees!Name;
Employees!Salary) returns the salary of the employee whose name is
given as the value of the *FullName* text field.

The formula above references *Name* and *Salary*, which are
both named
values, belonging to the *Employees* screen, whose formulas are
arrays specifying the contents of these table columns. These named values and
their formulas can be created and maintained using a data editor in Calcapp Creator.

Technically, this function returns the array element in the second array with the same position as an element in the first array which matches a given value. XLOOKUP(30, { 10, 20, 30 }, { 100, 200, 300 })XLOOKUP(30; { 10; 20; 30 }; { 100; 200; 300 }) returns 300, because the sought value, 30, is the third element in the first array, which causes XLOOKUP to return the third element in the second array, 300.

## Looking up table data

Consider the following table:

Kind | Color | Price |
---|---|---|

Shirt | Red | 29 |

Shirt | Blue | 39 |

Jacket | Blue | 149 |

Now, assume that the table is represented as three named values,
*Kind*, *Color* and *Price* (which Calcapp Creator's
data editor produces).

(*Kind* uses the formula { "Shirt", "Shirt",
"Jacket" }{ "Shirt"; "Shirt";
"Jacket" }, *Color* uses { "Red", "Blue",
"Blue" }{ "Red";
"Blue"; "Blue" } and *Price* uses { 29, 39, 149 }{ 29; 39; 149 }.)

This formula returns the color of the product costing $39, as there is only one:

Had there been multiple products costing $39, the first match would have been returned.

Multiple criteria can also be used. This formula returns the price of a blue shirt:

Above, the formula fragment (Kind = "Shirt")(Kind = "Shirt") returns the logical array { TRUE, TRUE, FALSE }{ TRUE; TRUE; FALSE }, as the first two products are shirts. (Color = "Blue")(Color = "Blue") returns { FALSE, TRUE, TRUE }{ FALSE; TRUE; TRUE }, as the second and third products are blue. (Kind = "Shirt") && (Color = "Blue")(Kind = "Shirt") && (Color = "Blue") returns { FALSE, TRUE, FALSE }{ FALSE; TRUE; FALSE }, as only the second product is both a shirt and is blue.

In other words, the XLOOKUP formula above is equivalent to this formula:

XLOOKUP looks up the index of the first TRUE value, and returns the corresponding price, $39.

## XLOOKUP, INDEX and MATCH

XLOOKUP offers many optional features. It can be instructed to accept not
only exact matches, but the next smallest element, or the next largest
element. When matching text, XLOOKUP supports wildcards, making it possible
to, say, find values that begin or end with a certain text string. (The
wildcard "*at" matches both "cat" and "bat", for instance.) Collectively,
these options make up the **match mode** given to the function.

XLOOKUP supports searches starting with the last item first, which is useful
when there are duplicates and you prefer a value found at the end of the
array to one found at the beginning. It can also use a binary search if the
lookup array is known to be ordered, which is faster. These options make up
the **search mode** given to XLOOKUP.

Under the hood, XLOOKUP uses XMATCH to find the position in the first array. It accepts the same match and search modes as XMATCH. Combining XMATCH with INDEX (which returns an array element at a certain position) can fully replace XLOOKUP.

These formulas are fully equivalent:

XMATCH accepts the same match and search modes as XLOOKUP. For detailed documentation on these modes, refer to the documentation of XMATCH. The match and search modes of XLOOKUP are also discussed in the formula examples that accompany this text.

## Unsuccessful searches and the fallback value

This formula returns a `#N/A`

error, because 40 cannot be found in
the first array:

ISNA can be used to determine if a value could not be found. This formula returns TRUE:

The fourth parameter may be used to set a custom fallback value, which is
returned if the sought value cannot be found in the lookup array instead of
`#N/A`

. This formula returns 99:

As the match and search modes make up the fifth and sixth parameters to
XLOOKUP, you are forced to provide an explicit fallback value if you provide
a match or search mode. To use a match or search mode while continuing to use
the `#N/A`

error as a fallback value (which works with ISNA), use the NA function.

These formulas are equivalent:

The second formula has all its optional parameters set to their default values.

## Determining matches with a formula fragment

There is a version of XLOOKUP that determines matches using a formula fragment, rather than testing for equality. Consider this formula:

The formula above works well if the exact value 30 should be found, but what if an element should be found that is greater than 10 and is also an odd number? This is straight-forward if a formula fragment is provided as the first parameter:

The formula above returns 40, which is the corresponding element to 15 in the lookup array, which is both greater than 10 and is an odd number.

Use this version of XLOOKUP if you need the full power of the formula language to find matches. This version of XLOOKUP is documented below.

## Examples

Returns the salary of the employee whose name is given as the value of
the *FullName* text field. *Name* and *Salary* are
both named values, belonging to
the *Employees* screen, whose formulas are arrays specifying the
contents of these table columns. These named values and their formulas
can be created and maintained using a spreadsheet-like editor in Calcapp
Creator.

Returns 300, because the value 30 is the second element in the lookup array, which causes XLOOKUP to return the second element in the result array, which is 300.

Returns an `#N/A`

error, because the value 40 cannot be found
in the lookup array.

Returns 99, because the value 40 cannot be found in the lookup array and
99 is set as the fallback value. When a fallback value is set explicitly,
it is returned instead of `#N/A`

when a value cannot be found.

Returns 99, because the value 40 cannot be found in the lookup array and 99 is set as the fallback value. The fifth parameter is the match mode, where 0 calls for an exact match, which is the default match mode. This match mode can be specified as either 0 or MatchMode.ExactMatchMode,Exact.

Returns 300, because the match mode has been set to "exact or next smallest" through the fifth parameter, -1. As 40 cannot be found in the lookup array, the next smallest element is instead used, which is 30. Its corresponding value in the result array is 300, causing 300 to be returned. This match mode can be specified as either -1 or MatchMode.ExactOrNextSmallestMatchMode,ExactOrNextSmallest.

Returns an `#N/A`

error. The match mode has been set to "exact
or next smallest" through the fifth parameter, -1, but neither the sought
value itself, 10, or any smaller value is part of the lookup array,
causing the fallback value to be returned. The fallback value is
specified explicitly as NA()NA(), which returns an
`#N/A`

error. This match mode can be specified as either -1 or
MatchMode.ExactOrNextSmallestMatchMode,ExactOrNextSmallest.

Returns 200, because the match mode has been set to "exact or next largest" through the fifth parameter, 1. As 10 cannot be found in the lookup array, the next largest element is instead used, which is 20. Its corresponding value in the result array is 200, causing 200 to be returned. This match mode can be specified as either 1 or MatchMode.ExactOrNextLargestMatchMode,ExactOrNextLargest.

Returns an `#N/A`

error. The match mode has been set to "exact
or next largest" through the fifth parameter, 1, but neither the sought
value itself, 40, or any larger value is part of the lookup array,
causing the fallback value to be returned. The fallback value is
specified explicitly as NA()NA(), which returns an
`#N/A`

error. This match mode can be specified as either 1 or
MatchMode.ExactOrNextLargestMatchMode,ExactOrNextLargest.

Returns 200, because the wildcard search string `ca?`

matches
the "cat" element in the lookup array, which causes XLOOKUP to return the
array element in the result array at the corresponding position, 200. The
third parameter, 2, requests the match mode "wildcard," which matches
text strings with special characters against the elements of a text
array. `?`

matches any character, which explains why
`ca?`

matches "cat". `*`

matches zero, one, or
multiple characters. `Colo*r ad*s are great?`

matches both
"Color ads are great!" and "Colour adverts are great?". This match mode
can be specified as either 2 or MatchMode.WildcardMatchMode,Wildcard.

Returns "c", because the second 1 element, corresponding to "c", is found before the first 1 element, corresponding to "a". The reason the second 1 element is found first is due to the sixth parameter, -1, which requests the search mode "last to first." As its name implies, it searches the lookup array starting with the last element and ending with the first element. This search mode can be specified as either -1 or SearchMode.LastToFirstSearchMode,LastToFirst.

Returns "a", because the first 1 element, corresponding to "a", is found before the second 1 element, corresponding to "c". The reason the first 1 element is found first is due to the sixth parameter, 1, which requests the search mode "first to last" (which is the default). As its name implies, it searches the lookup array starting with the first element and ending with the last element. This search mode can be specified as either 1 or SearchMode.FirstToLastSearchMode,FirstToLast.

Returns 4, because the sought value, 30, is the third element in the lookup array, and 4 is the third element in the result array. The sixth parameter, 2, requests the search mode "binary search, ascending," which can only be used when the lookup array is known to be sorted in ascending order (with the smallest elements appearing first). Binary search is much faster than the other search modes when the array is known to be ordered. This search mode can be specified as either 2 or SearchMode.BinarySearchAscendingSearchMode,BinarySearchAscending.

Returns 4, because the sought value, 30, is the first element in the lookup array, and 4 is the first element in the result array. The sixth parameter, -2, requests the search mode "binary search, descending," which can only be used when the lookup array is known to be sorted in descending order (with the largest elements appearing first). Binary search is much faster than the other search modes when the array is known to be ordered. This search mode can be specified as either -2 or SearchMode.BinarySearchDescendingSearchMode,BinarySearchDescending.

Returns the array { NA(), 20 }{ NA(); 20 }. The first value, 7,
cannot be found in the lookup array, causing the first element of the
return value array to be an `#N/A`

error. The second value, 3,
is the second element of the lookup array, causing the corresponding
result array element, 20, to be used as the second element of the return
value array. This example demonstrates that XLOOKUP can look up multiple
values per invocation.

**XLOOKUP**(ItemLocator, LookupArray, ResultArray, FallbackValue?)

**XLOOKUP**(ItemLocator; LookupArray; ResultArray; FallbackValue?)

## ItemLocator

A formula fragment which is invoked once for every array element being
tested, and is expected to return TRUE if the element matches and FALSE
otherwise. To do its work, it has access to the values
`Element`

(the array element currently being tested),
`Index`

(the numeric position of the array element currently
being tested, starting at 1) and `Source`

, the lookup array.

## LookupArray

The array which may or may not contain the sought value. This array
must be identical in size to the `resultArray`

parameter.

## ResultArray

The array containing results. If the sought value can be found in the
lookup array, the element from the result array is returned whose
position matches that of the found element. This array must be
identical in size to the `lookupArray`

parameter.

## FallbackValue

The value to return if the sought value cannot be found in the lookup
array. If omitted, an `#N/A`

error is used.

## Returns

The element in the result array which has the same position as the
element in the lookup array located by the first parameter formula. If
the value cannot be found, the given fallback value is returned
instead. If there is no fallback value, an `#N/A`

error is
returned.

Returns the element in the result array which has the same position as the element in the lookup array located by a given formula fragment. XLOOKUP(Element = 10, { 20, 15, 10 }, { 100, 200, 300 })XLOOKUP(Element = 10; { 20; 15; 10 }; { 100; 200; 300 }) returns 300, because the first parameter identifies the 10 element — the third element in the lookup array — as the matching item, and the third element in the second array is 300.

This version of XLOOKUP is Calcapp-specific.

## Unsuccessful searches and the fallback value

This formula returns an `#N/A`

error, because no element of the
lookup array is equal to 30:

This formula instead returns 99, as a fallback value is specified as the fourth parameter, which is returned if there is no match:

## Using a formula fragment versus a regular value

These formulas are equivalent and locate the element 30 in the lookup array:

Using a simple value as the first parameter, which the first formula above does, is arguably simpler. Using a formula fragment as the first parameter, though, which the second formula does, has the advantage that it unlocks the full power of the formula language when determining which element of the lookup array matches.

Consider this formula:

The formula above identifies the element 15, which is both odd and is greater than 10, at position two. As a result, it returns the second element of the result array, 200.

When determining what element matches, the first parameter has access to any operator and function, including the logical operators && (logical "and"), || (logical "or") and ! (logical negation), making for very powerful matching.

The first parameter formula fragment is run once for every array element to
be tested, and is expected to return TRUE if the element is a match, and
FALSE otherwise. To do its work, it has access to the values
`Element`

(the array element currently being tested),
`Index`

(the numeric position of the array element currently being
tested, starting at 1) and `Source`

, the lookup array.

These values can be renamed using `->`

:

The formula sketch above renames `Element`

to `V`

,
`Index`

to `I`

and `Source`

to
`S`

.