Excel Xlookup Function Explained With Examples

Introduction _ XLOOKUP

The Excel Xlookup function is the latest function which only available for Office 365 users. If you have ever used Vlookup function then It would be easy for you to undertsand it. Xlookup function works better than Vlookup and has more functionality and flexibility or you can say it is a replacement of Vlookup.

Arguments

This function has 6 arguments as follows:

1. Lookup_value = Required
2. Lookup_array = Required
3. Return_array = Required
4. If_not_found = Optional
5. Match_mode = Optional
6. Search_mode = Optional

What Xlookup Function can do for you

1. Unlike Vlookup, Xlookup can lookup values from right or left both sides of table
2. You can lookup horizontally and vertically
3. Xlookup can lookup for next smallest and largest values By Default
4. Xlookup looks for an exact match but it can be changed if you would like to change
5. It can return entire row or column instead of one single value.

Let’s Understand Xlookup With few Examples

Example #1 _ XLOOKUP Simple Exact Match

Let’s say you have table as shown in below image and you want to look for an employee name associated with the ID. So for this you will enter this formula =XLOOKUP(B2, B5:B16, C5:C16)

1. Lookup_array = B2
2. Lookup_array = B5:B16
3. Return_array = C5:C16

Example #2 _ Multiply Results with Exact Match

If you want to bring all the result associated with ID such as date of birth, email address, etc.. You can modify the formula and instead of selecting single column as a return array, select whole table except the lookup value. So enter this formula =XLOOKUP(B2, B5:B16, C5:F16) and you will get all the result in once.

Example #3 _ If Not Found

If you enter any ID which does not exist in this table so the formula will give this erorr #N/A. In this case you can use 4th argument which is ” If not Found ” to set any custom message instead of that error. Let’s say you want to write “Not Found” instead of that error if the ID does not exist in this table, so you need to enter formula =XLOOKUP (B2, B5:B16, C5:F16, “Not Found”).

Example #4 _ Combining XLOOKUP and TRANSPOSE

We can also use Xlookup combining with Transpose function to get results by row instead of columns. In below example we want to bring the results by row instead of columns so we will use Transpose function with Xlooup to get the results. The formula we need to enter is =TRANSPOSE( XLOOKUP( I4, B5:B16, C5:F16)).

Example #5 _ Perform Left Lookup with XLOOKUP

As before we used to use INDEX MATCH Formula to lookup left or above but now you do not need to use two formulas to lookup left. You can easily use XLOOKUP instead of INDEX MATCH. You can see the below example where I used XLOOKUP to get employee ID which is located on Left side of employees name. The formula I entered is =XLOOKUP( C2, C5:C16, B5:B16).

You might be interested to learn more:

1. How to Change Text Case in Excel
2. How to Generate Barcodes in Excel – Complete Guide
3. How to Convert Numbers into Words in Excel Without VBA
4. Filter Multiple Columns and Criteria Using Advance Filter
5. Easy and Fast Way to Merge Cells in Excel