Two-way lookups and multiple criteria
Updated · Works in: Excel 2021+, Microsoft 365, Google Sheets · Every example checked before release
Short answer
Two-way: =INDEX(grid, MATCH(row, rows, 0), MATCH(col, cols, 0)). Several conditions: =XLOOKUP(1, (A=x)*(B=y), result) or INDEX/MATCH(1, …, 0). Brackets and tiers: XLOOKUP(value, boundaries, results, , -1) or VLOOKUP(…, TRUE) on a sorted table.
Once a simple lookup isn't enough, three patterns cover nearly every case: two-way grids, lookups on several conditions, and approximate matches for brackets.
Row and column: INDEX with two MATCHes
INDEX(grid, MATCH(row_label, rows, 0), MATCH(column_label, columns, 0)). With XLOOKUP, nest one inside the other. To fill a whole report grid with one formula, lock the labels halfway: $H2 keeps the rep's column, I$1 keeps the month's row.
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Rep | Hugo Martin | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | Month | Aug | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | Sales | $15,800 | |
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | |||
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | |||
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | |||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | |||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | |||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
Several conditions at once
Multiply the conditions, then look up 1: XLOOKUP(1, (A=x)*(B=y)*(C=z), result). Without XLOOKUP, INDEX(result, MATCH(1, conditions, 0)) does the same.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Month | Region | Product | Units | Revenue | Region | South | |
| 2 | Jan 2026 | North | Fern | 40 | $380.00 | Product | Monstera | |
| 3 | Jan 2026 | North | Monstera | 12 | $408.00 | Month | Apr 2026 | |
| 4 | Jan 2026 | South | Fern | 35 | $332.50 | Units | 16 | |
| 5 | Jan 2026 | South | Monstera | 9 | $306.00 | |||
| 6 | Feb 2026 | North | Fern | 44 | $418.00 | |||
| 7 | Feb 2026 | North | Monstera | 15 | $510.00 | |||
| 8 | Feb 2026 | South | Fern | 31 | $294.50 | |||
| 9 | Feb 2026 | South | Monstera | 11 | $374.00 | |||
| 10 | Mar 2026 | North | Fern | 52 | $494.00 | |||
| 11 | Mar 2026 | North | Monstera | 14 | $476.00 | |||
| 12 | Mar 2026 | South | Fern | 38 | $361.00 | |||
| 13 | Mar 2026 | South | Monstera | 13 | $442.00 | |||
| 14 | Apr 2026 | North | Fern | 47 | $446.50 | |||
| 15 | Apr 2026 | North | Monstera | 18 | $612.00 | |||
| 16 | Apr 2026 | South | Fern | 42 | $399.00 | |||
| 17 | Apr 2026 | South | Monstera | 16 | $544.00 |
Brackets and tiers: approximate match
For grades, bands and tiers, find the largest boundary at or below the value: XLOOKUP(value, boundaries, results, , -1) or VLOOKUP(value, table, 2, TRUE) with the table sorted ascending.
| A | B | C | D | E | F | G | H | |
|---|---|---|---|---|---|---|---|---|
| 1 | Student | Class | Hours | Score | Grade | From | Grade | |
| 2 | Noor | A | 2 | 48 | F | 0 | F | |
| 3 | Mateo | B | 5 | 66 | C | 50 | D | |
| 4 | Hana | A | 8 | 81 | B | 60 | C | |
| 5 | Ollie | B | 3 | 55 | D | 70 | B | |
| 6 | Zara | A | 10 | 93 | A | 85 | A | |
| 7 | Ivan | B | 6 | 72 | B | |||
| 8 | Lea | A | 1 | 39 | F | |||
| 9 | Omar | B | 7 | 77 | B | |||
| 10 | Sara | A | 9 | 88 | A | |||
| 11 | Theo | B | 4 | 61 | C | |||
| 12 | Yuki | A | 6 | 70 | B | |||
| 13 | Bea | B | 8 | 84 | B |
Check yourself
One real exercise from the “Advanced lookups” unit. Checked on our server; no account needed.I1 holds a row number and I2 a column number. In I3, return that cell of the sales grid C2:E9 with INDEX.
- Use INDEX
| A | B | C | D | E | F | G | H | I | |
|---|---|---|---|---|---|---|---|---|---|
| 1 | Rep | Region | Jul | Aug | Sep | Target | Row | 6 | |
| 2 | Amara Osei | North | $12,400 | $13,100 | $11,800 | $36,000 | Column | 3 | |
| 3 | Ben Fischer | South | $9,800 | $10,250 | $12,100 | $30,000 | Sales | ||
| 4 | Lina Chen | East | $15,200 | $14,800 | $16,900 | $45,000 | |||
| 5 | Diego Alvarez | West | $8,700 | $9,100 | $8,400 | $28,000 | |||
| 6 | Eva Novak | North | $11,200 | $12,600 | $13,900 | $36,000 | |||
| 7 | Farid Haddad | South | $13,300 | $12,900 | $14,100 | $39,000 | |||
| 8 | Grace Kim | East | $10,100 | $9,600 | $10,800 | $33,000 | |||
| 9 | Hugo Martin | West | $14,500 | $15,800 | $15,100 | $42,000 |
Common mistakes
Adding conditions with + (that's OR) when you mean AND (multiply).
Approximate VLOOKUP on an unsorted table: it returns wrong rows without an error.
Mismatched range sizes between INDEX and the MATCH ranges.
Questions
Can VLOOKUP use two criteria?+
Not directly. Add a helper column that joins the keys, or use XLOOKUP/INDEX MATCH with multiplied conditions.
What does XLOOKUP's search mode -1 do?+
It searches from the last row up, so it finds the last match: `XLOOKUP(key, keys, results, , 0, -1)`. That's the most recent one only when the rows are sorted by date.
Reading is step one. Now make it stick.
Practise it in a five-minute lesson on a live sheet, with instant feedback. No account needed for the free units.