Excel lookup interview question: match two tables
The lookup task almost every Excel interview includes: bring a value across from a second table. The answer interviewers want, why VLOOKUP fails here, and a sheet that grades yours.
VLOOKUP function
VLOOKUP searches the first column of a table for a value and returns the value in the same row from a column you choose by number. Always end it with FALSE for an exact match.
Available in every version of Excel, Excel for the web and Google SheetsOn this page
VLOOKUP syntax
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])| Argument | Required | What it means |
|---|---|---|
lookup_value | Yes | What you're looking for. It must be in the first column of table_array. |
table_array | Yes | The table, starting with the column you search in. |
col_index_num | Yes | Which column of the table to return, counting the first as 1. |
range_lookup | No | FALSE for an exact match. Leave it out and VLOOKUP does an approximate match, which needs sorted data. |
Examples
Find a price by product code
The table is A2:C6. The price is its 3rd column, and FALSE asks for an exact match on the code in E2.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Find | Price | |
| 2 | A-101 | Keyboard | $49.00 | A-104 | $59.00 | |
| 3 | A-102 | Mouse | $19.00 | |||
| 4 | A-103 | Monitor | $189.00 | |||
| 5 | A-104 | Webcam | $59.00 | |||
| 6 | A-105 | Headset | $79.00 |
=VLOOKUP(E2,A2:C6,3,FALSE) → $59.00Return a different column
Same table, column 2 instead of 3: the product name.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Find | Product | |
| 2 | A-101 | Keyboard | $49.00 | A-104 | Webcam | |
| 3 | A-102 | Mouse | $19.00 | |||
| 4 | A-103 | Monitor | $189.00 | |||
| 5 | A-104 | Webcam | $59.00 | |||
| 6 | A-105 | Headset | $79.00 |
=VLOOKUP(E2,A2:C6,2,FALSE) → WebcamPractise it
New data, so you can't copy the answer above. We check the number your formula returns, then change the data to make sure it still holds.
Your task: In F2, return the stock level of the SKU in E2, or #N/A when that SKU isn't in the list.
Graded live| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | SKU | Item | In stock | SKU | In stock | |
| 2 | SK-01 | Notebook | 340 | SK-04 | ||
| 3 | SK-02 | Pen | 1200 | |||
| 4 | SK-03 | Stapler | 45 | |||
| 5 | SK-04 | Folder | 610 | |||
| 6 | SK-05 | Marker | 0 |
Write the formula in the bar above, then press Check or Enter.
Hint
Search A2:C6 for E2 and return column 3. End with FALSE for an exact match.
Solution
=VLOOKUP(E2,A2:C6,3,FALSE) Any formula that returns the same result passes.
Common mistakes
- Leaving out FALSE. Without it VLOOKUP does an approximate match, which assumes the first column is sorted and can quietly return the wrong row. Write FALSE (or 0) as the fourth argument for codes, names and IDs.
- Looking up a value that isn't in the first column. VLOOKUP only searches the table's first column. Start table_array at the column you search in, or use XLOOKUP, which can look left.
- A column number past the end of the table. Asking for column 4 of the 3-column table A2:C6 returns #REF!.
- Hard-coded column numbers that go stale. Insert a column inside the table and column 3 is suddenly something else. XLOOKUP or INDEX/MATCH name the return column as a range instead.
- Numbers stored as text. 1041 and the text '1041' don't match, so you get #N/A. Convert one side, for example with VALUE or by re-entering the IDs as numbers.
FAQ
Why does VLOOKUP return #N/A?
The value isn't in the first column of the table, or it is but differs slightly: extra spaces, a number stored as text, or a typo. Check the first column, then wrap the formula in IFNA if a missing value is expected.
What does FALSE mean in VLOOKUP?
FALSE asks for an exact match. Without it, VLOOKUP does an approximate match that only works on sorted data and can return the wrong row.
Can VLOOKUP look to the left?
No. It returns columns to the right of the one it searches. Use XLOOKUP or INDEX and MATCH to look left.
Should I still learn VLOOKUP?
Yes. It's in almost every existing workbook and in many interview tests. For new formulas in Microsoft 365 or Excel 2021, XLOOKUP is easier and safer.
Guides that use VLOOKUP
Worked problems with practice sheets.
#REF! error in Excel: what it means and how to fix it
#REF! means a formula points at a cell that isn't there: deleted, or past the edge of a range. The usual causes and fixes, with a practice sheet.
#N/A error in Excel: what it means and how to fix it
#N/A means a lookup didn't find what it was looking for. Here are the six usual causes, how to spot each one on the sheet, and the fix, with a practice sheet.
Know VLOOKUP? Prove it on a real sheet.
Practise VLOOKUP and the functions around it in a live grid that grades your result and explains your mistakes.
Runs in your browser · Graded on your result · No install