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.
MATCH function
MATCH tells you where a value sits in a list: its position, not the value itself. MATCH("A-104", A2:A6, 0) returns 4 because A-104 is the 4th item.
Available in every version of Excel, Excel for the web and Google SheetsOn this page
MATCH syntax
=MATCH(lookup_value, lookup_array, [match_type])| Argument | Required | What it means |
|---|---|---|
lookup_value | Yes | What you're looking for. |
lookup_array | Yes | A single row or column to search. |
match_type | No | 0 for an exact match. 1 (the default) and -1 are approximate matches that need sorted data. |
Examples
Where is a code in the list?
A-104 is the 4th code in A2:A6, so MATCH returns 4.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Code | Product | Find | Position | |
| 2 | A-101 | Keyboard | A-104 | 4 | |
| 3 | A-102 | Mouse | |||
| 4 | A-103 | Monitor | |||
| 5 | A-104 | Webcam | |||
| 6 | A-105 | Headset |
=MATCH(D2,A2:A6,0) → 4Find a month's column in a header row
MATCH works across a row too. Mar is the 3rd month in B1:E1, so MATCH returns 3, ready to give INDEX as a column number.
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar | Apr |
| 2 | East | 120 | 135 | 150 | 160 |
| 3 | |||||
| 4 | Month | Mar | |||
| 5 | Column | 3 |
=MATCH(B4,B1:E1,0) → 3Common mistakes
- Leaving out the 0. MATCH's default is an approximate match on sorted data. For codes and names, always write 0.
- Expecting the value instead of its position. MATCH returns a number such as 4. To get what's on that row, wrap it in INDEX.
- Searching a whole table. lookup_array must be one row or one column. MATCH(E2,A2:C6,0) returns #N/A.
FAQ
What does the 0 in MATCH mean?
It asks for an exact match. 1 finds the largest value less than or equal to the one you're looking for, and -1 the smallest value greater than or equal to it; both need sorted data.
Is MATCH case-sensitive?
No. "webcam" and "Webcam" match.
Guides that use MATCH
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 MATCH? Prove it on a real sheet.
Practise MATCH 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