VLOOKUP practice exercises
Look up a value in the first column of a table and return a value from another column, with an exact or approximate match.
How VLOOKUP works
VLOOKUP finds a value in the first column of a table and returns the value from another column in the same row. It takes four arguments: what to look for, the table, which column to return (counting from the table's first column), and FALSE for an exact match.
=VLOOKUP(A2,$E$2:$G$50,3,FALSE) looks up the ID in A2 in the first column of E2:G50 and returns the third column. The dollar signs keep the table in place when you copy the formula down.
Most VLOOKUP mistakes are one of three things: leaving out FALSE, so Excel returns the nearest match instead of an exact one; a lookup value that is not in the first column of the table; and IDs stored as text in one place and as numbers in the other. The exercises below cover a price lookup, a number key, approximate match for bands, filling prices into an order list with a locked table, and a lookup that fails on a stray space.
- 1Look up a price with VLOOKUPIn G2, use VLOOKUP to find the Price of the code in F2. Ask for an exact match.
- 2VLOOKUP a number keyPremiumIn G2, use VLOOKUP to return the Salary of the employee whose ID is in F2.
- 3VLOOKUP with an approximate matchPremiumIn E2, find the commission rate for the sales in D2. The table lists where each band starts, so use VLOOKUP with TRUE for an approximate match.
- 4Bring prices into an order listPremiumIn C2, look up the price of the code in B2 in the price table E2:F5. Lock the table with $ so the formula can be filled down the order list: VLOOKUP(B2,$E$2:$F$5,2,FALSE).
- 5Fix a VLOOKUP that fails on a spacePremiumThe code in F2 was pasted from an email and ends with a space, so a plain VLOOKUP returns #N/A. In G2, look up the price with TRIM(F2) as the lookup value.
Functions in this topic
Read the guide
- Excel test for a job interview: what it looks like and how to practice
- Free Excel course online: learn Excel from your first formula to job-ready
- VLOOKUP vs XLOOKUP vs INDEX MATCH: which lookup to use
- Excel for finance and accounting: the skills you actually use
- Excel practice data: free datasets with tasks and answers
- Best Excel courses online in 2026: 10 places to learn Excel, ranked
- Udemy vs Coursera vs LinkedIn Learning for Excel: which to pick
Questions
How do I use VLOOKUP with another sheet?
Put the sheet name and an exclamation mark in front of the table: =VLOOKUP(A2,Prices!$A$2:$C$50,3,FALSE). If the sheet name has spaces, wrap it in single quotes, like 'Price List'!$A$2:$C$50. The lookup value stays on your current sheet, and only the table lives on the other one.
What is the difference between VLOOKUP and XLOOKUP?
XLOOKUP looks in one range and returns from another, so it can look to the left, it does not break when you insert a column, and it matches exactly by default. VLOOKUP needs a column number and FALSE for an exact match. XLOOKUP needs Excel 365 or Excel 2021, so VLOOKUP is safer in files shared with older versions.
Why is VLOOKUP not working when the value is there?
The two values look the same but are not. Remove stray spaces with TRIM(A2), and remove a non-breaking space from a web page with SUBSTITUTE(A2,CHAR(160),""). If a number is stored as text, convert it with VALUE(A2) or multiply it by 1, so both sides have the same type.