formulastreak

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.

Q3 sales team · edit I3 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultI3 = $15,800
ABCDEFGHI
1RepRegionJulAugSepTargetRepHugo Martin
2Amara OseiNorth$12,400$13,100$11,800$36,000MonthAug
3Ben FischerSouth$9,800$10,250$12,100$30,000Sales$15,800
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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.

Monthly sales by region · edit H4 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultH4 = 16
ABCDEFGH
1MonthRegionProductUnitsRevenueRegionSouth
2Jan 2026NorthFern40$380.00ProductMonstera
3Jan 2026NorthMonstera12$408.00MonthApr 2026
4Jan 2026SouthFern35$332.50Units16
5Jan 2026SouthMonstera9$306.00
6Feb 2026NorthFern44$418.00
7Feb 2026NorthMonstera15$510.00
8Feb 2026SouthFern31$294.50
9Feb 2026SouthMonstera11$374.00
10Mar 2026NorthFern52$494.00
11Mar 2026NorthMonstera14$476.00
12Mar 2026SouthFern38$361.00
13Mar 2026SouthMonstera13$442.00
14Apr 2026NorthFern47$446.50
15Apr 2026NorthMonstera18$612.00
16Apr 2026SouthFern42$399.00
17Apr 2026SouthMonstera16$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.

Exam results · edit E2 and watch it recalculate
Click a cell (or use the arrow keys) to insert its address · Enter shows the resultE2 = F
ABCDEFGH
1StudentClassHoursScoreGradeFromGrade
2NoorA248F0F
3MateoB566C50D
4HanaA881B60C
5OllieB355D70B
6ZaraA1093A85A
7IvanB672B
8LeaA139F
9OmarB777B
10SaraA988A
11TheoB461C
12YukiA670B
13BeaB884B

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
Select I3 and type = to start its formula (in the cell or the formula bar)
ABCDEFGHI
1RepRegionJulAugSepTargetRow6
2Amara OseiNorth$12,400$13,100$11,800$36,000Column3
3Ben FischerSouth$9,800$10,250$12,100$30,000Sales
4Lina ChenEast$15,200$14,800$16,900$45,000
5Diego AlvarezWest$8,700$9,100$8,400$28,000
6Eva NovakNorth$11,200$12,600$13,900$36,000
7Farid HaddadSouth$13,300$12,900$14,100$39,000
8Grace KimEast$10,100$9,600$10,800$33,000
9Hugo MartinWest$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.

Practise it now