XLOOKUP is the latest addition to Excel lookup family of functions. It is much more functional excel formula than VLOOKUP or HLOOKUP. In this post, let us explore all about this new EXCEL XLOOKUP function with Syntax and examples.
XLOOKUP formula is available only on Office 365 at the time of writing this. It may roll out to other version in the coming months.
What is XLOOKUP? How it is different from VLOOKUP or HLOOKUP?
XLOOKUP allows us to search for a value in a range or table and return the matching result. It is similar to VLOOKUP/HLOOKUP but, it offers so much more. For example, VLOOKUP can do only vertical lookup and HLOOKUP will do only horizontal lookup. XLOOKUP can be used for vertical or horizontal lookup. We will explore more on this in our example section.
XLOOKUP has just 3 mandatory parameters and 3 optional parameters.
The mandatory parameters are
- The value you are looking for.
- The list where the value to be searched.
- The list where you want the result.
Advantages of XLOOKUP in EXCEL
- XLOOKUP is simple and easy to use: You just need to write =XLOOKUP(What you want to find, list where you want to find, result list). You will get the answer. It is less error prone as well. No need to use the index number or define on whether you want to exact or relative match.
- Supports right to left lookup: By default, this function has support for right to left lookup.
- IFERROR is built in as parameter: Most of the times, we will wrap VLOOKUP formula with IFERROR to hide the #N/A. However, XLOOKUP has 4th parameter in which you can define the default output if the the value is not found.
- Reference as output not the value: This means that, you combine XLOOKUP outputs with other formulas to get innovative outputs.
XLOOKUP Syntax
Basic formula with mandatory fields
=XLOOKUP(What you want to find, list where you want to find, result list)
Optional Parameters
Only 3 parameters are mandatory for XLOOKUP formula. However, you can use optional 4th, 5th and 6th parameters to explore additional features.
4th Parameter in XLOOKUP: IF not found: This parameter can be used to define what to do incase if search value is not found. Instead of returning #N/A, you can get your optional output.
5th Parameter in XLOOKUP: Match Mode or type: By default, match type is exact match. However, you can choose between Exact match or next smaller item or next larger item or Wildcard character match.
6th Parameter in XLOOKUP-Match direction: By default direction is top down. If you want bottom up, you define it in the 6th parameter.
XLOOKUP in EXCEL examples
Let us take the sample data sheet below.
Example 1: As a alternative to VLOOKUP:
=XLOOKUP(A2,F2:F10,G2:G10)
This will look for the value in A2 in the range F2 to F10 and return the corresponding value in the range G2:G10. The result will be “Sales”.
Example 2: Right to Left Lookup
One of the advantage with XLOOKUP is right to left lookup. So to get the name, use formula as given below.
=XLOOKUP(A2,F2:F10,E2:E10)
This will look for the value in A5 in the range F2 to F10 and return the corresponding value from the range E2 to E10. That is “Jill”.
Example 3: Lookup bottom up
When there is a duplicate values in lookup range, you can choose first from the top or first one from the bottom by using the 6 parameter. -1 to bottom first and 1 for top first.
=XLOOKUP(A8,G2:G10,H2:H10,,,-1)
This will look for the value in A8 in the range G2 to G10 and return the corresponding value from the range H2 to H10. As we have selected -1 as 6th parameter, it will pick first one from last. The result will be 17-Apr-2020.
Example 4: Handling Not found error
In XLOOKUP, you can define what to do incase the search value is not found in the lookup list by entering the 4th parameter.
=XLOOKUP(A11,F2:F10,E2:E10,”Not Found”)
This will look for the value in A11 in the range F2 to F10 and return the corresponding value from the range E2 to E10. As the ID “SA104” not available in the range F2 to it will return “Not Found”. You can define any value or a different formula. For example, you can define another XLOOKUP to look for the data in different table as well.
