Exact Match | Not Found | Approximate Match | Left Lookup | Horizontal Lookup | Multiple Values | Last Match

If you have Excel 365 or Excel 2021, use XLOOKUP instead of VLOOKUP. The XLOOKUP function is easier to use and has some additional advantages.

Exact Match

By default, the XLOOKUP function in Excel 365/2021 performs an exact match.

1. The XLOOKUP function below looks up the value 53 (first argument) in the range B3:B9 (second argument).

2. Next, it simply returns the value in the same row from the range E3:E9 (third argument).

3. Here’s another example. Instead of returning the salary, the XLOOKUP function below returns the last name (replace E3:E9 with D3:D9) of ID 79.

Not Found

If the XLOOKUP function cannot find a match, it returns a #N/A error.

1. For example, the XLOOKUP function below cannot find the value 28 in the range B3:B9.

2. Use the fourth argument of the XLOOKUP function to replace the #N/A error with a friendly message.

Approximate Match

Let’s take a look at an example of the XLOOKUP function in approximate match mode.

1. The XLOOKUP function below looks up the value 85 (first argument) in the range B3:B7 (second argument). There’s just one problem. There’s no value 85 in this range.

2. Fortunately, the value -1 (fifth argument) tells the XLOOKUP function to find the next smaller value. In this example, the value 80.

3. Next, it simply returns the value in the same row from the range C3:C7 (third argument).

Note: use 1 instead of -1 for the fifth argument to find the next larger value. In this example, the value 90. The XLOOKUP function also works with unsorted data. In this example, there’s no need to sort the scores in ascending order.

Left Lookup

Instead of using INDEX and MATCH in Excel to perform a left lookup, simply use the XLOOKUP function. For example, take a look at the XLOOKUP function below.

Explanation: the XLOOKUP function looks up the last name and returns the ID.

Horizontal Lookup

Instead of using the HLOOKUP function in Excel to perform a horizontal lookup, simply use the XLOOKUP function.

Explanation: to quickly copy this formula to the other cells, use absolute references ($E$4:$H$4 and $E$6:$H$6).

Multiple Values

The XLOOKUP function in Excel 365/2021 can return multiple values.

1. First, the XLOOKUP function below looks up the ID and returns the first name (nothing new).

2. Replace C6:C12 with C6:E12 to return the first name, last name and salary.

Note: the XLOOKUP function, entered into cell C3, fills multiple cells. Wow! This behavior in Excel 365/2021 is called spilling.

Last Match

By default, the XLOOKUP function performs a first to last search (nothing new). To perform a last to first search, set the sixth argument to -1 (see example below).

Explanation: this XLOOKUP function returns the salary of Mia Reed, not Mia Clark.