4.5 Two-way Lookup (Row and Column)
Two-way lookup with INDEX and two MATCH with example — in 30 seconds
What: A two-way lookup finds the cell where a chosen row and a chosen column cross.
Why: Managers pick a city and a month from drop-downs and want one number. No hand searching in a grid.
Tiny example: G1 = Nashik, G2 = Oct. The formula returns ₹30,100. =INDEX($B$2:$D$4, MATCH(G1,$A$2:$A$4,0), MATCH(G2,$B$1:$D$1,0))
Two-way lookup with INDEX and two MATCH with example — 30 सेकंदात
काय: Two-way lookup म्हणजे निवडलेली row आणि निवडलेला column जिथे एकमेकांना भेटतात ती cell शोधणं.
का: Manager drop-down मधून शहर आणि महिना निवडतो आणि त्याला एक number हवा असतो. Grid मध्ये हाताने शोधायचं नाही.
छोटं उदाहरण: G1 = Nashik, G2 = Oct. Formula उत्तर ₹30,100 देतो. =INDEX($B$2:$D$4, MATCH(G1,$A$2:$A$4,0), MATCH(G2,$B$1:$D$1,0))
Two-way lookup with INDEX and two MATCH with example — 30 सेकंड में
क्या: Two-way lookup यानी चुनी हुई row और चुना हुआ column जहाँ मिलते हैं वह cell ढूँढना.
क्यों: Manager drop-down से शहर और महीना चुनता है और उसे एक number चाहिए. Grid में हाथ से ढूँढना नहीं.
छोटा उदाहरण: G1 = Nashik, G2 = Oct. Formula जवाब ₹30,100 देता है. =INDEX($B$2:$D$4, MATCH(G1,$A$2:$A$4,0), MATCH(G2,$B$1:$D$1,0))
First look at the data (before → after):
Expected result — Answer for a few picks
| G1 City | G2 Month | G3 Result (₹) |
|---|---|---|
| Nashik | Oct | 30,100 |
| Pune | Nov | 52,300 |
| Nagpur | Sep | 25,900 |
About this data: Teaching sample for a fictional shop “HomeNest Supplies”. Figures and names (e.g. Rohan Patil) are made up for this lesson on two way lookup row and column.
Before table and formula as text
Before — Sales grid A1:D4 (₹, fictional)
| A: City | B: Sep | C: Oct | D: Nov |
|---|---|---|---|
| Pune | 41,200 | 45,600 | 52,300 |
| Nashik | 28,400 | 30,100 | 33,800 |
| Nagpur | 25,900 | 27,700 | 31,200 |
About this data: Demo table only — not real customer or company data. Built to practise two way lookup row and column.
Formula / click path
G1: Nashik G2: Oct
G3: =INDEX($B$2:$D$4, MATCH(G1,$A$2:$A$4,0), MATCH(G2,$B$1:$D$1,0))
Do it step by step:
- Type the grid in A1:D4.
- Type Nashik in G1 and Oct in G2 (or make drop-downs with Data Validation).
- In G3, type
=INDEX($B$2:$D$4, MATCH(G1,$A$2:$A$4,0), MATCH(G2,$B$1:$D$1,0)). - The first MATCH finds the row, the second MATCH finds the column.
- Change G1 to Pune and G2 to Nov. G3 should change to 52,300.
स्टेप बाय स्टेप करून बघा:
- A1:D4 मध्ये grid type करा.
- G1 मध्ये Nashik आणि G2 मध्ये Oct टाका (किंवा Data Validation ने drop-down बनवा).
- G3 मध्ये
=INDEX($B$2:$D$4, MATCH(G1,$A$2:$A$4,0), MATCH(G2,$B$1:$D$1,0))type करा. - पहिला MATCH row शोधतो, दुसरा MATCH column शोधतो.
- G1 ला Pune आणि G2 ला Nov करा. G3 मध्ये 52,300 यायला हवं.
स्टेप बाय स्टेप करके देखें:
- A1:D4 में grid type करें.
- G1 में Nashik और G2 में Oct डालें (या Data Validation से drop-down बनाएँ).
- G3 में
=INDEX($B$2:$D$4, MATCH(G1,$A$2:$A$4,0), MATCH(G2,$B$1:$D$1,0))type करें. - पहला MATCH row ढूँढता है, दूसरा MATCH column ढूँढता है.
- G1 को Pune और G2 को Nov करें. G3 में 52,300 आना चाहिए.
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 |
About this data: Made-up rows so you can copy the pattern for two way lookup row and column. Currencies may use ₹, £, € or $.
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.
Loading practice tasks...
- Row of a city (Easy): Find the position of "Nagpur" in A2:A4.
- Column of a month (Easy): Find the position of "Nov" in B1:D1.
- Resort: row of Suite (Easy): Find the position of "Suite" in Rates!A2:A4.
- Resort: column of Festival (Easy): Find the position of "Festival" in Rates!B1:D1.
- Row and column number (Medium): Get row 1, column 3 of B2:D4.
- Resort: row 2, column 2 (Medium): Get row 2, column 2 of Rates!B2:D4.
- Two-way lookup (Hard): Find sales for the city in G1 and month in G2.
- Nagpur in Sep (Hard): Find sales for "Nagpur" in "Sep".
- Resort: Deluxe at festival (Hard): Find the price of "Deluxe" in "Festival".
- Resort: 2 nights in a Suite (Hard): Find the cost of 2 nights in a "Suite" in "Peak season".
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
