PlayInfoBox

Home /Utilities /How to Use the XLOOKUP Function in Excel

How to Use the XLOOKUP Function in Excel

Tap a step to follow along in order.

  1. Click the cell where you want the result

    Click an empty cell where you want the looked-up value to appear.

  2. Type the XLOOKUP formula

    Enter it in the form =XLOOKUP(lookup_value, lookup_array, return_array).

  3. Set the lookup and return ranges separately

    Unlike VLOOKUP, XLOOKUP lets you point to the lookup column and the return column independently, so the return column can sit to the left or right of the lookup column.

  4. Add a message for when nothing is found

    Fill in the fourth, optional argument with something like "Not found" so a custom message appears instead of a #N/A error.

  5. Why it is useful

    VLOOKUP could only search to the right of its first column, but XLOOKUP frees the lookup and return columns from any fixed position, making tables more flexible and letting error handling happen inside the formula itself.

No more counting columns left to right

VLOOKUP always searched the leftmost column of a range and returned a value from a column further to the right, which meant restructuring a table just to make a lookup work. XLOOKUP removes that constraint entirely by letting the lookup column and the return column be pointed to as two separate, independent arguments, so the return column can sit anywhere relative to the lookup column.

It defaults to an exact match, not an approximation

Older lookup functions often needed a fourth argument set to FALSE or 0 to force an exact match, and forgetting it silently returned the wrong row. XLOOKUP defaults to an exact match automatically, and only switches to approximate or wildcard matching when the optional match_mode argument is deliberately set, which makes accidental partial matches far less likely.

Frequently Asked Questions

Can I use XLOOKUP in older versions of Excel?

No -- XLOOKUP is only available in Microsoft 365 and Excel 2021 or later. In older versions, an INDEX and MATCH combination works as a substitute.

Can it look up more than one value at once?

Yes -- setting the return range to span multiple columns lets a single XLOOKUP formula pull back several columns of values at once.