XLOOKUP

XLOOKUP Function Explained

XLOOKUP function is used for looking up a value in a range, and return its corresponding value.

XLOOKUP is a SUPER-FUNCTION, which combines the best features from various popular Excel functions, such as VLOOKUP, INDEX MATCH, IFERROR/IFNA, HLOOKUP and many more!

Here are some exclusive features which make XLOOKUP one of the best functions in Excel:

  • Search values starting from the last item in the array instead of the first!
  • Perform Vertical/Horizontal Lookups (No need to use HLOOKUP anymore…)
  • Lookup column can be after the return column (we had to use INDEX MATCH for that, until today)
  • Use wildcards to find partial matches
  • Use approximate match for numerical/date values (find next smallest/largest value)
  • Return multiple values for one match instead of only one, using Excel’s brand new Dynamic Array system.
  • Define which value to return in case the value we looked up wasn’t found (similar to IFERROR/IFNA)

Click here to practice XLOOKUP on our online Excel practice platform! 

Example

Syntax

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

lookup_value – What is the value we are looking for?

lookup_array – Where should we lookup our value?

return_array – From where should the corresponding value be returned? This could be a column, row or even a range – Meaning we can return more than one cell per match. Note that unlike VLOOKUP, and similar to INDEX MATCH – return_array can be before the lookup array 🙂

[if_not_found] – optional argument – What to return in case the value is not found. We can type a value, text (for example: “Value not found!” or even use a cell reference/function.

[match_mode] – optional argument – XLOOKUP has multiple match modes:

0 – Exact Match – Return the exact match – This is the default option.

-1 – Return the next smallest item, if the value was not found.

1 – Return the next largest item, if the value was not found.

2 – Wildcard match, using the ?, * and ~ signs. Unless we use this argument, we can’t perform wildcard search (opposed to VLOOKUP, where we can do wildcard search without using any special argument 🙁 )

[search_mode] – optional argument – Which search mode should be used:

1 – Search the array from first to last (For example, in a column – Search from top to bottom) – this is the default option.

-1 – Search from the last item to the first item in the array (For example, search bottom to top in a column).

Also, we can perform Binary Searches using the Binary Search algorithm:

2 – Binary search in ascending order – Make sure that lookup_array is sorted in ascending order in order to get correct results.

-2 – Binary search in descending order – Make sure that lookup_array is sorted in descending order in order to get correct results.

 

Practice XLOOKUP function online

Exercise #1 – Basic XLOOKUP usage

 

Exercise #2 – Return last matching value

Use search_mode argument in order to return the last matching value from your data!

 

Exercise #3 – Return all matching contents

Now, let’s try to return all matching contents using XLOOKUP & Excel’s Dynamic Array capabilities!

 

#Exercise 4 – Value not found

Now, let’s try to return a custom message in case our requested value was not found. This replaces the need to use IFERROR / IFNA formulas.

 

#Exercise 5 – Horizontal Lookup

We can use XLOOKUP to perform Horizontal Lookups, just like in HLOOKUP function:

 

Exercise #6 – Approximate match with XLOOKUP

How about using approximate match, in case a specific number or date is not found? While VLOOKUP allows us to use approximate match for the next smallest value, XLOOKUP allows us to find both the next smallest value and the next largest value!