4.5 Two-way Lookup (Row and Column)
A two-way lookup finds the value at the intersection of a row label and a column label.
Sheet CityMonth, A1:E5 (fictional sales):
| City | Aug | Sep | Oct | Nov |
|---|---|---|---|---|
| Pune | ₹4,12,500 | ₹5,86,200 | ₹4,95,300 | ₹6,10,800 |
| Nashik | ₹1,48,900 | ₹1,96,400 | ₹1,62,700 | ₹2,21,300 |
| Nagpur | ₹1,71,200 | ₹2,05,600 | ₹1,80,900 | ₹2,48,100 |
| Kolhapur | ₹1,02,300 | ₹1,39,800 | ₹1,11,400 | ₹1,57,600 |
City in H1 = Nashik, month in H2 = Oct.
INDEX + MATCH + MATCH (all versions):
=INDEX($B$2:$E$5, MATCH(H1,$A$2:$A$5,0), MATCH(H2,$B$1:$E$1,0)) → ₹1,62,700
Nested XLOOKUP (Microsoft 365 / Excel 2021+):
=XLOOKUP(H2, $B$1:$E$1, XLOOKUP(H1, $A$2:$A$5, $B$2:$E$5))
The inner XLOOKUP returns the whole Nashik row; the outer one picks the Oct column from it.
Ravindra Bagale's Tip
Two-way lookup madhe khup students row aani column che MATCH ulte lavtat – city cha MATCH column headers madhe shodhtat. Lakshat theva: INDEX(data, row MATCH, column MATCH) – aadhi row, mag column. H1 aani H2 madhe drop-down (Module 2) lavla ki ha chhota interactive report hoto.
Practice task
Add drop-downs for City and Month and build the two-way lookup. Add a third cell showing "Nashik sales in Oct: ₹1,62,700" using TEXT.