Wow! This behavior in Excel 365/2021 is called spilling. Note: the XLOOKUP function, entered into cell C3, fills multiple cells. Replace C6:C12 with C6:E12 to return the first name, last name and salary. First, the XLOOKUP function below looks up the ID and returns the first name (nothing new).Ģ. The XLOOKUP function in Excel 365/2021 can return multiple values.ġ. For example, take a look at the XLOOKUP function below.Įxplanation: the XLOOKUP function looks up the last name and returns the ID. Instead of using INDEX and MATCH in Excel to perform a left lookup, simply use the XLOOKUP function. In this example, there's no need to sort the scores in ascending order.

The XLOOKUP function also works with unsorted data.

Note: use 1 instead of -1 for the fifth argument to find the next larger value. Next, it simply returns the value in the same row from the range C3:C7 (third argument). Fortunately, the value -1 (fifth argument) tells the XLOOKUP function to find the next smaller value. The XLOOKUP function below looks up the value 85 (first argument) in the range B3:B7 (second argument). Let's take a look at an example of the XLOOKUP function in approximate match mode.ġ. Use the fourth argument of the XLOOKUP function to replace the #N/A error with a friendly message. For example, the XLOOKUP function below cannot find the value 28 in the range B3:B9.Ģ. If the XLOOKUP function cannot find a match, it returns a #N/A error.ġ. Instead of returning the salary, the XLOOKUP function below returns the last name (replace E3:E9 with D3:D9) of ID 79. Next, it simply returns the value in the same row from the range E3:E9 (third argument).ģ.

Fuzzy lookup excel mac