VLOOKUP/XLOOKUP: formulas, shortcuts & solved examples
What it is
Lookup functions find a value in one table and return related information from the same row. They are used daily in offices to pull prices, names, departments or rates from a master sheet into a report. VLOOKUP is the classic function; XLOOKUP is the newer, more flexible replacement available in recent versions of Excel.
Key formulas and rules
VLOOKUP
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
- Searches only the first column of
table_array. col_index_numcounts columns from the left of the table, starting at 1.range_lookup: FALSE (or 0) for exact match; TRUE (or omitted) for approximate match, which requires the first column sorted ascending.- Cannot look to the left of the search column.
XLOOKUP
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
- Exact match by default.
- Return column can be left or right of the lookup column.
- Built-in
if_not_foundtext replaces IFERROR. search_modeof -1 searches from the last row upward, useful for finding the latest entry.
INDEX-MATCH
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Works in all Excel versions and can look left.
Which one to use
- Use XLOOKUP if everyone who opens the file has a recent version of Excel (Microsoft 365 or Excel 2021 onwards). It is easier to read and less fragile.
- Use INDEX-MATCH when the file must work in older versions and you need to look left or want a column reference that survives inserted columns.
- Use VLOOKUP for quick, simple lookups in older files or when a team standard already relies on it.
Approximate match logic
With approximate match, VLOOKUP returns the row with the largest value that is less than or equal to the lookup value. This is ideal for tax slabs, grade bands, discount tiers and commission tables, but only when the first column is sorted from smallest to largest. XLOOKUP achieves the same with match_mode set to -1 (exact match or next smaller item), and it does not need sorted data.
Error handling
=IFERROR(VLOOKUP(...), "Not found") hides #N/A when a value is missing.
Shortcuts
- Lock the table with F4: after selecting the table range, press F4 to make it absolute (
$A$2:$D$500) so it does not shift when you copy the formula down. - Always type FALSE for IDs: for employee codes, invoice numbers or PAN, use exact match. Approximate match is only for slabs and bands.
- Use whole columns in XLOOKUP:
=XLOOKUP(A2, Master!A:A, Master!C:C)keeps working when new rows are added to the master.
Solved examples
Assume a sheet Master with Employee ID in column A, Name in B, Department in C and Monthly Salary in D, rows 2 to 200.
Example 1: Pull a name with VLOOKUP
In a report, cell A2 holds the ID E1045. Get the employee's name.
=VLOOKUP(A2, Master!$A$2:$D$200, 2, FALSE)
- Searches column A for E1045 and returns the value from the second column (Name).
Example 2: Look left with XLOOKUP
You know the employee's name in cell F2 and need the ID, which is to the left of the Name column.
=XLOOKUP(F2, Master!B2:B200, Master!A2:A200, "Not found")
- VLOOKUP cannot do this without rearranging columns; XLOOKUP can.
Example 3: Approximate match for commission slabs
A slab table in H2:I5 has sales thresholds and rates: 0 → 0%, 1,00,000 → 2%, 3,00,000 → 4%, 5,00,000 → 5%. A salesperson's sales are ₹3,60,000 in B2.
=VLOOKUP(B2, $H$2:$I$5, 2, TRUE)
- Excel finds the largest threshold not greater than ₹3,60,000, which is ₹3,00,000, and returns 4%.
- Commission = ₹3,60,000 × 4% = ₹14,400.
Example 4: Handle missing IDs
Some IDs in a new joining list are not yet in the master. Show "New joinee" instead of #N/A.
=IFERROR(VLOOKUP(A2, Master!$A$2:$D$200, 4, FALSE), "New joinee")
or with XLOOKUP:
=XLOOKUP(A2, Master!A2:A200, Master!D2:D200, "New joinee")
Common mistakes
- Leaving out the last argument in VLOOKUP, which silently switches to approximate match and returns wrong rows for unsorted data.
- Relative table references that drift down as the formula is copied, causing #N/A in lower rows.
- Hard-coded column numbers that break when someone inserts a column in the master sheet.
- Extra spaces or numbers stored as text in the lookup column, so values that look identical do not match. TRIM and VALUE help.
- Expecting VLOOKUP to return the second match. All three methods return the first match only, unless you change the search mode or use other functions.
Practice now: https://www.testsworld.com/mocks