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.
INDEX function
INDEX returns the value at a position you give it: the 3rd row of a column, or the cell where row 2 and column 3 of a table meet. Paired with MATCH, it becomes a lookup that works in every Excel version.
Available in every version of Excel, Excel for the web and Google SheetsOn this page
INDEX syntax
=INDEX(array, row_num, [column_num])| Argument | Required | What it means |
|---|---|---|
array | Yes | The range to pick from. |
row_num | Yes | Which row of the range, counting its first row as 1. |
column_num | No | Which column, for a range more than one column wide. |
Examples
The 3rd item in a list
INDEX counts from the top of the range, not the top of the sheet: row 3 of B2:B6 is B4.
| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Row | Product | |
| 2 | A-101 | Keyboard | $49.00 | 3 | Monitor | |
| 3 | A-102 | Mouse | $19.00 | |||
| 4 | A-103 | Monitor | $189.00 | |||
| 5 | A-104 | Webcam | $59.00 | |||
| 6 | A-105 | Headset | $79.00 |
=INDEX(B2:B6,E2) → MonitorINDEX and MATCH together
MATCH finds which row A-104 is on (the 4th), and INDEX returns the price from that row. Unlike VLOOKUP, the price column can be anywhere.
| 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 |
=INDEX(C2:C6,MATCH(E2,A2:A6,0)) → $59.00Practise 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, use INDEX and MATCH to return the city of the customer named in E2, or #N/A when that customer isn't in the list.
Graded live| Row | A | B | C | D | E | F |
|---|---|---|---|---|---|---|
| 1 | City | Customer | Orders | Customer | City | |
| 2 | Lisbon | Atlas Ltd | 14 | Corvo SA | ||
| 3 | Tirana | Brisk Co | 9 | |||
| 4 | Lyon | Corvo SA | 22 | |||
| 5 | Graz | Delta AG | 5 | |||
| 6 | Porto | Echo BV | 31 |
Write the formula in the bar above, then press Check or Enter.
Hint
MATCH(E2,B2:B6,0) gives the row of the customer. Wrap it in INDEX over the cities, A2:A6.
Solution
=INDEX(A2:A6,MATCH(E2,B2:B6,0)) Any formula that returns the same result passes.
Common mistakes
- Counting rows from the top of the sheet. INDEX(B2:B6,3) is B4, the 3rd row of the range, not row 3 of the sheet.
- Ranges that don't line up. In INDEX/MATCH, the INDEX range and the MATCH range must start on the same row, or every answer is shifted.
- Forgetting MATCH's 0. Without 0 as its last argument, MATCH does an approximate match and can return the wrong row.
FAQ
Is INDEX MATCH better than VLOOKUP?
Yes, in most ways: it can return a column to the left, it doesn't break when you insert columns, and it works on any column order. XLOOKUP does the same in one function if your Excel has it.
Why does INDEX return #REF!?
The row or column number is larger than the range. INDEX(B2:B6,7) asks for the 7th row of a 5-row range.
Guides that use INDEX
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.
XLOOKUP vs VLOOKUP: which should you use?
XLOOKUP does everything VLOOKUP does with fewer ways to go wrong. Here's the same lookup written both ways, the five differences that matter, and when VLOOKUP is still the right call.
Know INDEX? Prove it on a real sheet.
Practise INDEX 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