XLOOKUP function

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:

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:

XLOOKUP(39, Price, Color)XLOOKUP(39; Price; Color)

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:

XLOOKUP(TRUE, (Kind = "Shirt") && (Color = "Blue"), Price)XLOOKUP(TRUE; (Kind = "Shirt") && (Color = "Blue"); Price)

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(TRUE, { FALSE, TRUE, FALSE }, Price)XLOOKUP(TRUE; { FALSE; TRUE; FALSE }; Price)

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:

XLOOKUP(30, { 10, 20, 30 }, { 100, 200, 300 })XLOOKUP(30; { 10; 20; 30 }; { 100; 200; 300 })

INDEX({ 100, 200, 300 }, XMATCH(30, { 10, 20, 30 }))INDEX({ 100; 200; 300 }; XMATCH(30; { 10; 20; 30 }))

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:

XLOOKUP(40, { 10, 20, 30 }, { 100, 200, 300 })XLOOKUP(40; { 10; 20; 30 }; { 100; 200; 300 })

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

ISNA(XLOOKUP(40, { 10, 20, 30 }, { 100, 200, 300 }))ISNA(XLOOKUP(40; { 10; 20; 30 }; { 100; 200; 300 }))

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:

XLOOKUP(40, { 10, 20, 30 }, { 100, 200, 300 }, 99)XLOOKUP(40; { 10; 20; 30 }; { 100; 200; 300 }; 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:

ISNA(XLOOKUP(40, { 10, 20, 30 }, { 100, 200, 300 }))ISNA(XLOOKUP(40; { 10; 20; 30 }; { 100; 200; 300 }))

ISNA(XLOOKUP(40, { 10, 20, 30 }, { 100, 200, 300 }, NA(), MatchMode.Exact, SearchMode.FirstToLast))ISNA(XLOOKUP(40; { 10; 20; 30 }; { 100; 200; 300 }; NA(); MatchMode,Exact; SearchMode,FirstToLast))

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:

XLOOKUP(30, { 1, 5, 10, 15 }, { 10, 20, 30, 40 })XLOOKUP(30; { 1; 5; 10; 15 }; { 10; 20; 30; 40 })

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:

XLOOKUP((Element > 10) && ISODD(Element), { 1, 5, 10, 15 }, { 10, 20, 30, 40 })XLOOKUP((Element > 10) && ISODD(Element); { 1; 5; 10; 15 }; { 10; 20; 30; 40 })

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

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. 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.

XLOOKUP(30, { 20, 30 }, { 200, 300 })XLOOKUP(30; { 20; 30 }; { 200; 300 })

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.

XLOOKUP(40, { 20, 30 }, { 200, 300 })XLOOKUP(40; { 20; 30 }; { 200; 300 })

Returns an #N/A error, because the value 40 cannot be found in the lookup array.

XLOOKUP(40, { 20, 30 }, { 200, 300 }, 99)XLOOKUP(40; { 20; 30 }; { 200; 300 }; 99)

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.

XLOOKUP(40, { 20, 30 }, { 200, 300 }, 99, 0)XLOOKUP(40; { 20; 30 }; { 200; 300 }; 99; 0)

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.

XLOOKUP(40, { 20, 30 }, { 200, 300 }, NA(), -1)XLOOKUP(40; { 20; 30 }; { 200; 300 }; NA(); -1)

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.

XLOOKUP(10, { 20, 30 }, { 200, 300 }, NA(), -1)XLOOKUP(10; { 20; 30 }; { 200; 300 }; NA(); -1)

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.

XLOOKUP(10, { 20, 30 }, { 200, 300 }, NA(), 1)XLOOKUP(10; { 20; 30 }; { 200; 300 }; NA(); 1)

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.

XLOOKUP(40, { 20, 30 }, { 200, 300 }, NA(), 1)XLOOKUP(40; { 20; 30 }; { 200; 300 }; NA(); 1)

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.

XLOOKUP("ca?", { "cat", "dog" }, { 200, 300 }, NA(), 2)XLOOKUP("ca?"; { "cat"; "dog" }; { 200; 300 }; NA(); 2)

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.

XLOOKUP(1, { 1, 2, 1 }, { "a", "b", "c" }, NA(), 0, -1)XLOOKUP(1; { 1; 2; 1 }; { "a"; "b"; "c" }; NA(); 0; -1)

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.

XLOOKUP(1, { 1, 2, 1 }, { "a", "b", "c" }, NA(), 0, 1)XLOOKUP(1; { 1; 2; 1 }; { "a"; "b"; "c" }; NA(); 0; 1)

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.

XLOOKUP(30, { 10, 20, 30 }, { 7, 9, 4 }, NA(), 0, 2)XLOOKUP(30; { 10; 20; 30 }; { 7; 9; 4 }; NA(); 0; 2)

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.

XLOOKUP(30, { 30, 20, 10 }, { 4, 9, 7 }, NA(), 0, -2)XLOOKUP(30; { 30; 20; 10 }; { 4; 9; 7 }; NA(); 0; -2)

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.

XLOOKUP({ 7, 3 }, { 8, 3, 5, 14 }, { 10, 20, 30, 40 })XLOOKUP({ 7; 3 }; { 8; 3; 5; 14 }; { 10; 20; 30; 40 })

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.

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:

XLOOKUP(Element = 30, { 20, 15, 10 }, { 100, 200, 300 })XLOOKUP(Element = 30; { 20; 15; 10 }; { 100; 200; 300 })

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

XLOOKUP(Element = 30, { 20, 15, 10 }, { 100, 200, 300 }, 99)XLOOKUP(Element = 30; { 20; 15; 10 }; { 100; 200; 300 }; 99)

Using a formula fragment versus a regular value

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

XLOOKUP(30, { 20, 15, 10 }, { 100, 200, 300 })XLOOKUP(30; { 20; 15; 10 }; { 100; 200; 300 })

XLOOKUP(Element = 30, { 20, 15, 10 }, { 100, 200, 300 })XLOOKUP(Element = 30; { 20; 15; 10 }; { 100; 200; 300 })

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:

XLOOKUP((Element > 10) && ISODD(Element), { 20, 15, 10 }, { 100, 200, 300 })XLOOKUP((Element > 10) && ISODD(Element); { 20; 15; 10 }; { 100; 200; 300 })

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 ->:

XLOOKUP((V, I, S) -> ...)XLOOKUP((V; I; S) -> ...)

The formula sketch above renames Element to V, Index to I and Source to S.

Examples

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.

XLOOKUP(I -> (I > 10) && ISODD(I), { 15, 10 }, { 2, 1 })XLOOKUP(I -> (I > 10) && ISODD(I); { 15; 10 }; { 2; 1 })

Identifies 15 in the lookup array, which is both an odd number and is greater than 10, at position one, and consequently returns the first element of the result array, 2.