INDEX + MATCH
Look up any direction, no column counting
When to reach for this
Find an employee's salary by name when Name isn't the first column.
=INDEX(D2:D6, MATCH("Maria Lopez", B2:B6, 0))What this pattern does
Combines INDEX (which fetches a value by coordinates) with MATCH (which finds a position by value). The result is a lookup that works left-to-right, right-to-left, or anywhere in between, without rewriting column numbers whenever the sheet changes.
Interactive walkthroughWalk through INDEX
INDEX and MATCH are stronger together than VLOOKUP could ever be. MATCH finds where the match lives; INDEX fetches the value at that position. Watch the two pieces click together, step by step.
D2:D6return_arrayINDEX pulls the final value from here, the Salary column
MATCH("Maria Lopez", B2:B6, 0)row_num (dynamic)The inner MATCH finds which row, its result is passed straight to INDEX
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Emp ID | Name | Department | Salary | ||
| 2 | E001 | Sarah Chen | Engineering | $82,000 | =INDEX(D2:D6, MATCH("Maria Lopez", B2:B6, 0)) | |
| 3 | E002 | James Wu | Marketing | $68,000 | ||
| 4 | E003 | Maria Lopez | Finance | $98,000 | ||
| 5 | E004 | Tom Brown | Engineering | $55,000 | ||
| 6 | E005 | Anika Patel | HR | $125,000 |
You type the combo formula
In F2 you type =INDEX(D2:D6, MATCH("Maria Lopez", B2:B6, 0)). Excel always evaluates inner functions first, so MATCH runs before INDEX gets a number.
Key takeaway
Why pros prefer this: the lookup column can be anywhere, not just leftmost. Adding or reordering columns doesn't break the formula, because INDEX and MATCH point at explicit ranges, not column numbers.