Ravindra BagaleCourses & study guides Track your progress

Guides

How to Use XLOOKUP in Excel (Beginner Guide)

By , Excel & Data Analytics trainer LinkedIn Published: Last updated:

Read this guide in: English · मराठी · हिंदी

XLOOKUP searches a lookup column and returns a matching value from a return column. It is the modern, cleaner lookup for Excel 365 and Excel 2021.

Chala mitrano! VLOOKUP column number and “key must be left” confusion — XLOOKUP removes both. Why care? Faster shop price lookups. How? One Mumbai/Pune product sheet, before/after, then the formula. Stay with me.

Quick answer

Vertical steps:

  1. Confirm Microsoft 365 or Excel 2021.
  2. Put the Product ID in a cell (E2).
  3. Type =XLOOKUP(E2, lookup_column, return_column).
  4. Optional 4th argument: "Not found" instead of #N/A.
  5. Press Enter.
  6. Change E2 and watch the price update.
=XLOOKUP(E2, A2:A6, B2:B6, "Not found")

Language: English · मराठी · हिंदी

Before and after (look at the tables first)

Product ID Price (₹)
P101 40
P102 25
P103 60

Lookup P103 → result 60. Missing ID → Not found (if you set if_not_found).

Before XLOOKUP Product list before XLOOKUP.

Before

After XLOOKUP XLOOKUP returns 60 or Not found.

After

Why XLOOKUP?

  1. No column index number to miscount.
  2. Lookup and return columns can be anywhere (even left of the key).
  3. Built-in friendly message when nothing matches.

What is XLOOKUP?

XLOOKUP asks: find this value in this column; bring me the matching cell from that other column.

Formula parts

=XLOOKUP(E2, A2:A6, B2:B6, "Not found")
  1. E2 — lookup value.
  2. A2:A6 — where to search.
  3. B2:B6 — what to return (same height).
  4. "Not found" — optional if_not_found.

What happens (tiny numbers)

  1. E2 = P103.
  2. Excel finds P103 in the ID column.
  3. It returns the aligned price → 60.
  4. E2 = P199 → returns Not found (not a scary #N/A if you set argument 4).

Vertical practice steps

  1. Enter IDs in A and prices in B.
  2. E2 = P102.
  3. F2 = =XLOOKUP(E2,A2:A4,B2:B4,"Not found").
  4. Confirm 25.
  5. Clear E2 or type a fake ID — see your message.
  6. Move Price to column left of ID — XLOOKUP still works.

XLOOKUP vs VLOOKUP (quick)

Topic VLOOKUP XLOOKUP
Column index Required Not needed
Left lookup No Yes
if_not_found Wrap with IFNA Built-in argument
Excel version Older OK 365 / 2021

Ravindra Bagale's Tip

New workbook on Microsoft 365? Start with XLOOKUP. Still learn VLOOKUP and INDEX+MATCH for interviews and older office PCs. Don chaalat rahto — tools change, idea of “exact lookup” stays.

Practice task

  1. Build a city → delivery fee table (Mumbai, Pune, Nashik).
  2. XLOOKUP a city to get the fee.
  3. Add if_not_found = "Check city spelling".
  4. Break it once on purpose, then fix.

Learn it properly

Course lessons:

Related guides: VLOOKUP exact match · INDEX + MATCH · FILTER function

Samajla ka? XLOOKUP = search this column, return from that column, optional friendly miss message. Aata pudhe PivotTable create kara.

Frequently asked questions

What is XLOOKUP?

A modern lookup function that searches a lookup array and returns a matching value from a return array. It can look left or right.

XLOOKUP vs VLOOKUP?

XLOOKUP does not need a column index number, can return left of the key, and supports a default if_not_found argument.

Which Excel versions have XLOOKUP?

Microsoft 365 and Excel 2021. Excel 2016/2019 do not include it.

What if the key is missing?

Without if_not_found you get #N/A. Pass a fourth argument such as "Not found" for a clear message.

Can XLOOKUP return multiple columns?

Yes in Microsoft 365 by selecting a wider return array (spill). Start with one return column first.

Course lesson?

Chapter 4 — XLOOKUP basics and if_not_found. Linked below.