InfoparkDailyCAREER TOOLS

Excel · Lesson 5 of 8

Lookups: XLOOKUP, VLOOKUP & INDEX/MATCH

Fetching matching values from another table — the most asked Excel interview topic.

Lookups find a value in one table and return related data — an employee's department from their ID, a product's price from its code.

  • XLOOKUP(lookup, lookup_range, return_range, [if_not_found]) — modern, flexible, looks left or right, exact match by default (Excel 2021/365).
  • VLOOKUP(lookup, table, col_number, FALSE) — classic; the lookup column must be the first column, and you must pass FALSE for an exact match.
  • INDEX(return_range, MATCH(lookup, lookup_range, 0)) — works in every version and can look in any direction.

#N/A means “not found” — check for extra spaces (TRIM) or numbers stored as text.

Sample sheet & formulas

Type these in Excel or Google Sheets
Sheet "Staff":
     A       B          C
1    EmpID   Name       Dept
2    E101    Anu        Finance
3    E102    Rahul      Sales
4    E103    Meera      HR

Sheet "Report":   A2 = E103

B2:  =XLOOKUP(A2, Staff!A2:A4, Staff!C2:C4, "Not found")
C2:  =VLOOKUP(A2, Staff!A2:C4, 3, FALSE)
D2:  =INDEX(Staff!B2:B4, MATCH(A2, Staff!A2:A4, 0))
E2:  =XLOOKUP("E999", Staff!A2:A4, Staff!B2:B4, "Not found")

Results

B2 = HR
C2 = HR
D2 = Meera
E2 = Not found

Works in Microsoft Excel 365/2021 and, for almost every formula, in Google Sheets. Data is fictional.

Key points

  • XLOOKUP is the modern default.
  • VLOOKUP needs FALSE for exact match and looks right only.
  • INDEX/MATCH works everywhere, any direction.

Quick check

What should the last argument of VLOOKUP be for an exact match?

Practice task

Given a price list (Code, Item, Price) and an order sheet with codes and quantities, fetch each price with XLOOKUP and calculate line totals.

Interview corner

Limitations of VLOOKUP?

It can only look to the right, breaks when columns are inserted (hard-coded column number) and defaults to approximate match.

Why does a lookup return #N/A when the value looks present?

Common causes: trailing spaces, numbers stored as text vs numbers, or different spellings. TRIM, VALUE or consistent formatting fix it.

Further reading

Official documentation, linked for deeper study. These are outside sites; this lesson's text is our own.

Original lesson written by the InfoparkDaily team · © 2026 InfoparkDaily · Free to learn — no paid course or subscription.