Ravindra BagaleCourses & study guides मराठी Track your progress

Guides

VLOOKUP vs XLOOKUP vs INDEX-MATCH: Which Lookup Should You Use?

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

Raja Coffee Roasters has a product list with codes, names and prices. Rani needs the price of code P103. All three formulas below return ₹640. The difference shows up on harder questions: looking to the left, handling a missing code, or finding the *latest* price in a history list. Here the three formulas stop agreeing.

Use XLOOKUP if everyone who opens the file has Excel 2021, Excel 2024 or Microsoft 365: it looks left or right, defaults to exact match, has a built-in 'not found' value and can search from the bottom. Use INDEX with MATCH when the file must work in Excel 2016 or 2019: it is just as flexible, only longer to write. Use VLOOKUP only for simple lookups to the right in old files, and always give it FALSE for an exact match.

Download the practice file (vlookup-vs-xlookup-vs-index-match.xlsx)

Excel file, 9 KB. It has the sample data used below. All numbers in this workbook are fictional teaching data, not real company figures.

Quick answer

  1. Exact match: =XLOOKUP(code, codes, prices) or =INDEX(prices, MATCH(code, codes, 0)) or =VLOOKUP(code, table, 4, FALSE).
  2. Look left: XLOOKUP and INDEX/MATCH do it directly; VLOOKUP cannot without a trick.
  3. Not found: XLOOKUP's fourth argument; wrap the others in IFNA.
  4. Approximate (slabs): VLOOKUP with TRUE, XLOOKUP with match_mode -1, MATCH with 1, on a list sorted from smallest to largest.
  5. Last match: XLOOKUP with search_mode -1; VLOOKUP only finds the first.
  6. Pick XLOOKUP for new files; INDEX/MATCH when the file must open in Excel 2016 or 2019.

Six tasks, three formulas

TaskVLOOKUPXLOOKUPINDEX + MATCHResults
Price of P103=VLOOKUP(B4,A20:E27,4,FALSE)=XLOOKUP(B4,A20:A27,D20:D27)=INDEX(D20:D27,MATCH(B4,A20:A27,0))₹640 / ₹640 / ₹640
Code of 'Cold Brew Pack' (look left)Needs CHOOSE({1,2},...) to swap columns=XLOOKUP(B5,B20:B27,A20:A27)=INDEX(A20:A27,MATCH(B5,B20:B27,0))P104 / P104 / P104
Code P999 (not found)=IFNA(VLOOKUP(...),"Not found")=XLOOKUP(B6,A20:A27,D20:D27,"Not found")=IFNA(INDEX(...),"Not found")Not found (all three)
Discount for ₹3,200 (slabs)=VLOOKUP(B7,G20:H24,2,TRUE)=XLOOKUP(B7,G20:G24,H20:H24,,-1)=INDEX(H20:H24,MATCH(B7,G20:G24,1))5% / 5% / 5%
Latest price of P103=VLOOKUP(B8,K20:L25,2,FALSE) finds the first=XLOOKUP(B8,K20:K25,L20:L25,,0,-1)=LOOKUP(2,1/(K20:K25=B8),L20:L25)₹600 / ₹640 / ₹640
Filter Coffee in Jul (two-way)=VLOOKUP(B9,N20:Q23,MATCH(B10,N19:Q19,0),FALSE)=XLOOKUP(B9,N20:N23,XLOOKUP(B10,O19:Q19,O20:Q23))=INDEX(O20:Q23,MATCH(B9,N20:N23,0),MATCH(B10,O19:Q19,0))34 / 34 / 34

Task 5 is the trap: the price history has P103 three times. VLOOKUP stops at the first row (₹600, the April price). XLOOKUP with search_mode -1 searches from the bottom and returns the latest (₹640).

The differences that matter (from Microsoft's documentation)

PointVLOOKUPXLOOKUPINDEX + MATCH
Default matchApproximate (TRUE) if you leave out the last argument, so always type FALSE for exactExact matchMATCH defaults to 1 (approximate); type 0 for exact
Return columnA column number, which breaks when someone inserts a columnA range, so inserted columns do not break itA range
Look leftNoYesYes
If not found#N/A; wrap in IFNA or IFERRORBuilt-in if_not_found argument#N/A; wrap in IFNA
Search from the bottomNoYes (search_mode -1)Not with MATCH alone
Available inAll versionsExcel 2021, 2024, Microsoft 365, Excel for the web (Microsoft: not in Excel 2016 or 2019)All versions

Sources: VLOOKUP function, XLOOKUP function and Look up values with VLOOKUP, INDEX, or MATCH on Microsoft Support.

Which one should you use?

  1. New file, modern Excel for everyone: XLOOKUP. It is the shortest to read and the hardest to get wrong.
  2. File must open in Excel 2016 or 2019: INDEX with MATCH (and 0 for exact match). If someone opens an XLOOKUP file in those versions, the formula cannot be recalculated.
  3. Old file that already uses VLOOKUP: leave it if it works, but check every VLOOKUP has FALSE at the end and that nobody will insert columns inside the table.
  4. Slabs and bands (tax, discount, grade): any of the three, but sort the first column from smallest to largest and test the boundary values.

Ravindra Bagale's Tip

When you test a lookup, always try three inputs: one that exists, one that does not, and one that appears twice. Most lookup bugs hide in the second and third cases, and they only show up when the report is already with your manager.

Practice task

  1. Change the code in B4 to P106. Do all three formulas update?
  2. Type a code that is not in the list into B4. Which formulas show #N/A?
  3. Change the order value in B7 to exactly 2500 and then 2499. Which discount do you get each time, and why?
  4. Insert a new column between Product and Category in the Products table. Which formula in row 4 now returns the wrong column?
  5. Change B8 to P101. What do VLOOKUP and XLOOKUP return now?

Learn it properly

Course lessons:

Related guides: XLOOKUP for Beginners · XLOOKUP with Multiple Criteria (Product and City) · Excel vs SQL: Term-by-Term Mapping with Side-by-Side Examples

Frequently asked questions

Is XLOOKUP faster than VLOOKUP?

Speed depends on data size and settings; for most office files you will not notice a difference. Choose based on version support and how safe the formula is to edit.

Why does VLOOKUP return a wrong value instead of #N/A?

You probably left out the last argument. VLOOKUP then does an approximate match. Add FALSE as the fourth argument for an exact match.

Can INDEX MATCH look up with two conditions?

Yes: =INDEX(result,MATCH(1,(range1=value1)*(range2=value2),0)). In older Excel versions confirm it with Ctrl+Shift+Enter.

Does Google Sheets have XLOOKUP?

Yes. Google Sheets has XLOOKUP with lookup, result, missing value, match mode and search mode arguments.