XLOOKUP

The XLOOKUP function returns the value in the same row after finding a matching value in any column.
This looks up a value in any column of a table and returns a value in the same row but in a different column.
Alternatively it can look up a value in one row and return a value in the same column but in a different row.
This function is not case sensitive by default but you can provide an optional argument to change this. Replaces VLOOKUP and HLOOKUP

This function replaces both VLOOKUP and HLOOKUP.
This function defaults to exact match not closest match.
This function still works after inserting or deleting columns (or rows).
This function can lookup values on the left of the lookup_value column.
This function can search from the bottom up without sorting.

Return the Exact Match (vertical, unique values)

This can be used instead of the VLOOKUP function
When the "match_mode" argument is 0 an exact match is found.
If there is more than one occurrence, the first occurrence is found.
The definition of the first occurrence is the same regardless of whether the table is sorted.
The table does not have to be sorted when you are looking for an exact match. Return the Exact Match (horizontal, unique values)

This can be used instead of the HLOOKUP function Return the Exact Match (item does not exist)

When the "lookup_value" is not in the list #N/A is returned.
When the "match_mode" argument is 0 an exact match is found.
The table does not have to be sorted when looking for an exact match. Return the Exact Match (case sensitive)

The XLOOKUP function is not case sensitive by default.
This means that upper case and lower case characters are equal (for example Japan = JAPAN).
You can perform a case sensitive lookup when the list has not been sorted by using the EXACT function.
Unlike the VLOOKUP function this function does not have to be entered as an Array Formula.
When the "search_mode" argument is either 2 (or -2) the search is case sensitive but the "lookup_value" column must be sorted. Return the Exact Match (first occurrence, sorted or not sorted)

This function will return the first occurrence starting at the top.
When the "match_mode" argument is 0 an exact match is found.
The table does not have to be sorted to return the first occurrence. Return the Exact Match (last occurrence, sorted)

The last occurrence can be achieved when the "lookup_value" column is sorted into Ascending Order.
When the "search_mode" argument is either -1 or -2 the last occurrence is found.
The table has been sorted by Sales and then by Country in ascending order. Return the Exact Match (last occurrence, not sorted)

The last occurrence can be achieved when the "lookup_value" column is not sorted.
When the "search_mode" argument is either -1 or -2 the last occurrence is found. Return the Exact Match (with wildcard *)

An asterisk (*) matches any sequence of characters.
When the "match_mode" argument is 2 wildcard characters can be used.
You can have an asterisk as the first character. Return the Exact Match (with wildcard ?)

A question mark (?) matches any single character.
When the "match_mode" argument is 2 wildcard characters can be used.
You can have a question mark as the first character. Return the Closest Match (next smallest)

The next smallest value can be achieved when the "lookup_value" column is sorted into Ascending Order.
When the "match_mode" is -1 the next smallest value is found.
The table has been sorted by Sales into ascending order. Return the Closest Match (next largest)

The next largest value can be achieved when the "lookup_value" column is sorted into Descending Order.
When the "match_mode" is 1 the next largest value is found.
The table has been sorted by Sales into descending order. Combining with MATCH

A two-way lookup can be achieved using an XLOOKUP and MATCH combination.
A two-way lookup is when you return the intersection of a matching column with a matching row.

© 2019 Better Solutions Limited