Free demo · Microsoft Excel · Lesson 3 · Intermediate · Lookups

XLOOKUP: find any value in a table

Give XLOOKUP what to find, where to look and what to bring back. It searches the table and returns the matching value.

fx=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])
Code | Product | Price | | Code | Price P01 | Pen | 10 | | [fill=#fff2cc]P03 | =XLOOKUP(E2,A2:A6,C2:C6)→450 P02 | Notebook | 40 | | | P03 | School bag | 450 | | | P04 | Ruler | 15 | | | P05 | Water bottle | 120 | | |
  • E2lookup_value: what to find (code P03)
  • A2:A6lookup_array: where to look (the codes)
  • C2:C6return_array: what to bring back (the prices)
  • [if_not_found]optional message, e.g. "Not listed"

Why XLOOKUP beats VLOOKUP

  • ✓ Exact match by default
  • ✓ Looks left or right of the lookup column
  • ✓ Doesn't break when you insert columns
  1. 1
  2. 2
  3. 3
  4. 4
  5. Enrol

Tip: press → or Space to step through, ← to go back, R to replay.

About this lesson

XLOOKUP, available in Excel 2021, Excel 2024 and Microsoft 365, searches one range and returns the matching item from another. In =XLOOKUP(E2,A2:A6,C2:C6), E2 is the value to find, A2:A6 is where to look and C2:C6 holds the values to return, so the code P03 returns the price 450. Unlike VLOOKUP it uses an exact match by default, can return columns to the left, and keeps working when columns are inserted. The optional fourth argument sets a message such as “Not listed” when nothing matches.

Key points

  • The three required arguments of XLOOKUP
  • The optional if_not_found message
  • XLOOKUP vs VLOOKUP