📎 This article includes 1 downloadable practice file ↓
VLOOKUP answers “find this row, give me column 3”. Real tables often need both directions at once: the sales target for South in August, the rate for a 12-month FD at the senior citizen slab, the price of size L in red. The row and the column both change. That’s a two-way lookup, and INDEX with two MATCHes is the classic way to do it.
In this article
The table
| Region | Apr | May | Jun | Jul | Aug | Sep |
|---|---|---|---|---|---|---|
| North | 2,00,000 | 2,05,000 | 2,10,000 | 2,15,000 | 2,20,000 | 2,25,000 |
| South | 2,15,000 | 2,20,000 | 2,25,000 | 2,30,000 | 2,35,000 | 2,40,000 |
| East | 2,30,000 | … |
Regions are in A2:A5, months in B1:G1, numbers in B2:G5. The region you want is in J1 and the month in J2.
The formula
=INDEX(B2:G5, MATCH(J1, A2:A5, 0), MATCH(J2, B1:G1, 0))Read it from the inside out:
MATCH(J1, A2:A5, 0)— which row of the block is “South”? Answer: 2.MATCH(J2, B1:G1, 0)— which column is “Aug”? Answer: 5.INDEX(B2:G5, 2, 5)— give me the cell in row 2, column 5 of the numbers block: 2,35,000.
The XLOOKUP version (Excel 365 / 2021)
=XLOOKUP(J1, A2:A5, XLOOKUP(J2, B1:G1, B2:G5))The inner XLOOKUP returns the whole August column; the outer one picks the South row from it. Many people find this easier to read, and it gives you a built-in “not found” argument: =XLOOKUP(J1, A2:A5, XLOOKUP(J2, B1:G1, B2:G5, "No month"), "No region").
Variation: return a whole row or column
=INDEX(B2:G5, MATCH(J1, A2:A5, 0), 0) ' all six months for the region (spills in 365)
=SUM(INDEX(B2:G5, 0, MATCH(J2, B1:G1, 0))) ' total of one month across all regionsA column or row number of 0 in INDEX means “the whole thing”.
Variation: lookup on two row keys and a column
If rows are identified by two columns — say Region and Channel — build the row match with a Boolean array:
=INDEX(C2:H9, MATCH(1, (A2:A9=J1)*(B2:B9=J2), 0), MATCH(J3, C1:H1, 0))In older Excel this needs Ctrl+Shift+Enter; in 365 it works as is.
Where people go wrong
| Symptom | Cause | Fix |
|---|---|---|
| Wrong number, no error | MATCH ranges don’t line up with the INDEX block (e.g. A1:A5 vs B2:G5) | Start the row MATCH on the same row as the block, column MATCH on the same column |
| #N/A | “Aug” in the header is a real date formatted as “Aug”, but J2 is text | Use the same type on both sides — text headers, or match a date with a date |
| #N/A with “South ” | Trailing space in data or input | MATCH(TRIM(J1), …) or clean the source |
| Picks the wrong row | MATCH’s third argument left out (approximate match on unsorted data) | Always type the 0 |
| #REF! | MATCH found a position bigger than the INDEX block | Make both ranges the same size |
Practice
The combo practice workbook below has a Sales sheet of 200 orders and a task for every formula on this page. Type your formula in the yellow column; the check turns green when the answer matches. The Answers sheet has working versions.
More combinations: all formula combos · functions used here are explained in the Excel function course.
📎 Practice files for this article
- 📗Formula combinations practice workbook50 tasks: lookups, running totals, ranks, top-N, unique counts, FY, age, text extraction, list comparison, weighted averages, ageing, rolling totals, duplicates and working days — with automatic checks.⬇ XLSX · 39 KB
Free to use for learning. Files with macros (.bas) are plain text — import them with Alt+F11 → File → Import File, and always test on a copy.
Stuck on a step? Ask a question and the AI answers using this article.