XLOOKUP vs VLOOKUP vs INDEX/MATCH: which to use, and when

VLOOKUP: familiar, fragile

VLOOKUP searches the first column of a range and returns a value from a column counted to the right. It cannot look to the left, a hard-coded column number breaks when columns are inserted, and leaving the last argument out defaults to an approximate match, which silently returns wrong answers on unsorted data.

Always set the last argument to FALSE (or 0) for an exact match unless you specifically want an approximate lookup on sorted data.

INDEX/MATCH: flexible and robust

MATCH finds the position of a value; INDEX returns the value at that position in another range. Because the return range is referenced directly, it can look in any direction and does not break when columns are inserted. The same exact-match rule applies: use 0 as MATCH's match type.

XLOOKUP: the modern default

XLOOKUP takes a lookup value, a lookup array and a return array. It defaults to an exact match, can look in any direction, can return a custom value when nothing is found, and can search from the last item backward.

It is available in Microsoft 365 and Excel 2021 and later. Workbooks shared with users on older versions still need VLOOKUP or INDEX/MATCH.

• Need to look left, or insert columns later? Use XLOOKUP or INDEX/MATCH.
• Sharing with older Excel versions? Use INDEX/MATCH.
• Getting a wrong answer with no error? Check for an approximate match.