4.5 Two-way Lookup (Row and Column)
In short: A two-way lookup finds the value at the intersection of a row label and a column label.
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
In a two-way lookup, many students swap the row and column MATCH – they search for the city in the column headers. Remember: INDEX(data, row MATCH, column MATCH) – row first, then column. Put a drop-down (Module 2) in H1 and H2, and this becomes a small interactive report.
Ravindra Bagale's Tip – मराठी
Two-way lookup मध्ये बरेच students row आणि column चे MATCH उलटे लावतात – city चा MATCH column headers मध्ये शोधतात. लक्षात ठेवा: INDEX(data, row MATCH, column MATCH) – आधी row, मग column. H1 आणि H2 मध्ये drop-down (Module 2) लावला की हा छोटा interactive report होतो.
Ravindra Bagale's Tip – हिंदी
Two-way lookup में बहुत से students row और column के MATCH उल्टे लगा देते हैं – city का MATCH column headers में ढूँढते हैं. याद रखो: INDEX(data, row MATCH, column MATCH) – पहले row, फिर column. H1 और H2 में drop-down (Module 2) लगा दो तो यह एक छोटी interactive report बन जाती है.
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.