XLOOKUP or VLOOKUP or INDEX+MATCH

XLOOKUP is a combination of INDEX & MATCH functions.

Here is a summary of these three functions.

1. VLOOKUP: This function performs a vertical lookup from left to right. It defaults to an Approximate match. However, VLOOKUP only works when your lookup value is to the left of the return value you want.


2. INDEX+MATCH: This combination is a mixture of the two named functions. Unlike VLOOKUP, INDEX+MATCH can look up both ways, left to right as well as right to left.


3. XLOOKUP: Introduced in 2019, XLOOKUP can do everything VLOOKUP can do, and much more. It defaults to an Exact match. XLOOKUP can look up both ways, left to right as well as right to left.

In summary, VLOOKUP is simpler to use, INDEX+MATCH offers more flexibility as it does not require the lookup value to be to the left of the return value. XLOOKUP combines the simplicity of VLOOKUP with the flexibility of INDEX+MATCH. It is a powerful tool for data lookup.

To understand how to write all three functions, please download the file. We have compared all three functions.