XLOOKUP, VLOOKUP and INDEX MATCH practice
Lookups are the most asked-about formulas in Excel job tests. Practice finding a value in a staff list with XLOOKUP and VLOOKUP, use INDEX and MATCH together, chain two lookups, and handle values that aren't there.
Try it yourself
A live spreadsheet. Type a formula and press Enter to check it.
What is Priya Shah's salary?
Write your formula in the green cell, J2.
Click a cell to add it to your formula, drag to select a range, or click a column letter for the whole column.
All 11 lookups exercises
- 1→
Find a salary
FreeWhat is Priya Shah's salary?
- 2→
VLOOKUP by ID
FreeWhich department is employee E106 in? Use VLOOKUP.
- 3→
VLOOKUP a number
PremiumWhat is the salary of employee E108? Use VLOOKUP.
- 4→
Look up a date
PremiumWhen did Tom Becker start?
- 5→
INDEX and MATCH
PremiumFind the name of employee E103 using INDEX and MATCH.
- 6→
MATCH on its own
PremiumIn which position in the name list (B2:B9) is Sam Okafor?
- 7→
Look to the left
PremiumWhat is Hannah Lee's ID? The ID column is to the left of the names, so VLOOKUP can't do this one.
- 8→
Chain two lookups
PremiumWhat bonus % does Diego Ruiz get? Find his department, then look it up in the bonus table in G1:H5.
- 9→
Lookup inside a calculation
PremiumHow much is Lena Fischer's bonus in dollars? Multiply her salary by her department's rate from the bonus table in G1:H5.
- 10→
When it's not there
PremiumLook up the name of employee E110. That ID doesn't exist, so return "Not found" instead of an error.
- 11→
Two-way lookup
PremiumFind the value for employee E102 in the column headed Start date. Use MATCH for both the row and the column, so the formula keeps working if the columns move.
The lookups functions, with examples
Every example runs on staff-list.xlsx, the same sheet the exercises use, and the results are calculated from that data.
XLOOKUP
Full guide →Finds a value in one column and returns the matching value from another. It can look left and has a built-in “not found” value. Excel 2021 and later.
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])Example
=XLOOKUP("E104", A2:A9, B2:B9)returnsDiego RuizVLOOKUP
Full guide →Searches the first column of a table and returns the value from the column number you give. Use FALSE at the end for an exact match.
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])Example
=VLOOKUP("E103", A2:E9, 3, FALSE)returnsMarketingINDEX
Returns the value at a given position in a range. Paired with MATCH, it does lookups in any direction.
INDEX(array, row_num, [column_num])Example
=INDEX(B2:B9, 5)returnsPriya ShahMATCH
Returns the position of a value in a range. Use 0 as the last argument for an exact match.
MATCH(lookup_value, lookup_array, [match_type])Example
=MATCH("Tom Becker", B2:B9, 0)returns6
Common mistakes
- 1
Leaving out FALSE in VLOOKUP
Without it, VLOOKUP does an approximate match and, on unsorted data, can quietly return the wrong row. End with FALSE (or 0) unless you really want the nearest band, like a tax bracket.
- 2
Not locking the table
Copy
=VLOOKUP(G2,A2:E9,3,FALSE)down and the table moves with it, so lower rows return #N/A. Write it as$A$2:$E$9. - 3
#N/A from differences you can't see
An ID with a trailing space ("E101 "), or a number stored as text, won't match the value in the table. TRIM the lookup value, or convert both sides to the same type.