Excel track · Exercise 43 of 86 Paid
INDEX + MATCH: lookup either way
Return a value from any column, whichever side of the key it sits on.
Advanced
Employee directory
Read-only data · answer in E3
In E3, return the salary for the ID in D3 with INDEX and MATCH instead of VLOOKUP.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | ID | Name | Salary | Lookup | Result |
| 2 | E-101 | Ana | 61500 | ||
| 3 | E-102 | Bo | 57000 | E-103 | |
| 4 | E-103 | Cy | 88000 | ||
| 5 | E-104 | Dee | 54000 | ||
| 6 | E-105 | Eli | 49500 |
Reference answer
What it does
MATCH counts where a value sits inside the range you give it. E-103 sits in D3, and A2:A6 starts at E-101, so the ID lands on 3: the third cell down, which is sheet row 4. Ask MATCH on its own and 3 is all you get.
INDEX turns that count into the contents of a cell. Give it C2:C6 and the number 3, and it reads C4, where Cy's salary of 88000 sits. The answer goes in E3. The two calls are separate, so you can test the 3 before wrapping INDEX around it.
The zero in MATCH decides how it searches. Without it MATCH assumes a sorted list, so a missing ID can return a neighbouring row and the salary of the wrong person. With the zero it returns #N/A. Because each range is chosen separately, the salary column can sit left of the ID, which VLOOKUP cannot manage.
Syntax
=INDEX(return_column, MATCH(lookup_value, search_column, 0))Worked example
On Employee directory, =INDEX(C2:C6,MATCH(D3,A2:A6,0)) returns 88000.
=INDEX(C2:C6,MATCH(D3,A2:A6,0))Common errors
| Error | What it means and how to fix it |
|---|---|
#N/A | MATCH found no exact E-103 in A2:A6, usually because the ID carries a trailing space or is stored as text. |
#REF! | The row number from MATCH sits past the end of C2:C6, which happens when the two ranges cover different numbers of rows. |
#VALUE! | INDEX was handed no row number, or MATCH was given the whole table where a single column belongs. |
At work
Payroll and staff directories, where the column you need is chosen after the column you search.