How to Use XLOOKUP (The Formula That Makes VLOOKUP Obsolete)

Tutomod • Premium content made for you.

How to Use XLOOKUP (The Formula That Makes VLOOKUP Obsolete)

Sunday, August 9, 2026 · min read

VLOOKUP had one job and did it with quirks that tortured generations: search leftward impossible, column numbers breaking, silent errors.

XLOOKUP fixed everything and reads like English. If you learn one spreadsheet formula this decade, this is it.

Writing an XLOOKUP formula
Photo by Leeloo The First on Pexels

Before you start

  • Excel 2021 or Microsoft 365 (or Google Sheets, it works there too)
  • Any table of data to practice on

Step by step

  1. The shape of the formula. =XLOOKUP(what, where_to_look, what_to_return). Three ingredients instead of VLOOKUP's four, and no column counting: 'find this product code in column A, give me the price from column B' reads almost word for word.
  2. Your first lookup in practice. Say codes live in A2:A100 and prices in B2:B100. In D1 you type a code, in E1: =XLOOKUP(D1, A2:A100, B2:B100). The price appears. Change the code in D1, the answer follows, interactive spreadsheets begin.
  3. The not found safety net. The fourth argument catches misses gracefully: =XLOOKUP(D1, A2:A100, B2:B100, "Not in catalog") shows your message instead of the ugly #N/A. Professional sheets always include it.
  4. Search ANY direction (the VLOOKUP killer). Lookup column can be to the RIGHT of the return column: =XLOOKUP(D1, B2:B100, A2:A100) finds a price and returns its code. VLOOKUP physically couldn't: XLOOKUP doesn't care about column order.
    • Left, right, anywhere: freedom
    • Inserting columns later never breaks it, ranges move with the table
  5. Return MULTIPLE columns at once. Make the return range two columns wide: =XLOOKUP(D1, A2:A100, B2:C100) spills the price AND the category into two cells from one formula. It's quietly the most impressive party trick in modern Excel.
  6. Match modes for the advanced itch. The fifth argument offers wildcards (* for partial matches) and approximate matching for grade boundaries and tax brackets. Most users live happily in exact-match land, but the powers exist when the day demands them.
Looking up data in a spreadsheet
Photo by ThisIsEngineering on Pexels

Small tweaks, big difference

  • Convert ranges to Tables (Ctrl + T) and the formula reads =XLOOKUP(D1, Products[Code], Products[Price]): self documenting and auto growing as data expands.
  • Pair with dropdown lists (Data validation) for D1: click a product name from a menu, watch the price appear, dashboard energy in a single cell.
  • XLOOKUP works identically in Google Sheets: skills transfer both ways, employers on both platforms respect it.

What trips people up

  • Ancient Excel versions (2019 and earlier) lack XLOOKUP: check =XLOOKUP exists before building your masterpiece, or VLOOKUP becomes the compatibility fallback.
  • Performance note: huge files with tens of thousands of XLOOKUPs deserve INDEX/MATCH craftsmanship, but daily human-sized work never notices.

Quick answers

Should I stop learning VLOOKUP entirely?
Learn XLOOKUP deeply; recognize VLOOKUP when inheriting old workbooks. The new one is strictly better, the old one is strictly everywhere in legacy files.

Can it search rows instead of columns?
Yes, horizontally too: it's the universal lookup, HLOOKUP included in the obsolescence.

Keep reading

That covers it

Find this, over there, bring that back, message if missing. Four English ideas and a formula that finally obeys them: welcome to the better era.