4.4 INDEX and MATCH
Quick answer: the short, self-contained version of this lesson is the guide Excel INDEX and MATCH Lookup for Beginners.
INDEX and MATCH with example — in 30 seconds
What: MATCH finds the row number of a value. INDEX returns the value at that row from any column.
Why: Unlike VLOOKUP, it can look left, and inserting a new column does not break it.
Tiny example: BLK-KOP-01 is in row 5 of the Store IDs, so INDEX returns row 5 of the managers: Rani. =INDEX(Stores!$E$2:$E$8, MATCH(G2, Stores!$A$2:$A$8, 0))
INDEX and MATCH with example — 30 सेकंदात
काय: MATCH एखाद्या value चा row number शोधतो. INDEX त्या row ची कोणत्याही column मधली value देतो.
का: VLOOKUP सारखं याला फक्त उजवीकडे बघायचं बंधन नाही, आणि नवा column घातला तरी तो तुटत नाही.
छोटं उदाहरण: BLK-KOP-01 हा Store IDs मध्ये 5व्या row ला आहे, म्हणून INDEX managers मधली 5वी row देतो: Rani. =INDEX(Stores!$E$2:$E$8, MATCH(G2, Stores!$A$2:$A$8, 0))
INDEX and MATCH with example — 30 सेकंड में
क्या: MATCH किसी value का row number ढूँढता है. INDEX उस row की किसी भी column की value देता है.
क्यों: VLOOKUP की तरह इसे सिर्फ़ दाईं ओर देखने की मजबूरी नहीं, और नया column डालने पर भी यह नहीं टूटता.
छोटा उदाहरण: BLK-KOP-01 Store IDs में 5वीं row पर है, इसलिए INDEX managers की 5वीं row देता है: Rani. =INDEX(Stores!$E$2:$E$8, MATCH(G2, Stores!$A$2:$A$8, 0))
First look at the data (before → after):
Expected result — City Manager for each order
| G: Store ID | MATCH result | Q: City Manager |
|---|---|---|
| BLK-KOP-01 | 5 | Rani |
| AMN-SLP-01 | 6 | Salman |
| BLK-SBN-01 | 7 | Raja |
About this data: Teaching sample for a fictional shop “QuickCart Delivery”. Figures and names (e.g. Meera Joshi) are made up for this lesson on index and match.
Before table and formula as text
Before — Stores sheet (A: Store ID … E: City Manager)
| A: Store ID | C: City | D: Area | E: City Manager |
|---|---|---|---|
| BLK-PUN-01 | Pune | Kothrud | Ravindra Bagale |
| BLK-PUN-02 | Pune | Hinjewadi | Shraddha Bagale |
| BLK-NSK-01 | Nashik | College Road | Zoya |
| AMN-NGP-01 | Nagpur | Dharampeth | Amir |
| BLK-KOP-01 | Kolhapur | Rajarampuri | Rani |
| AMN-SLP-01 | Solapur | Murarji Peth | Salman |
| BLK-SBN-01 | Sambhaji Nagar | CIDCO | Raja |
About this data: Demo table only — not real customer or company data. Built to practise index and match.
Formula / click path
Q2: =INDEX(Stores!$E$2:$E$8, MATCH(G2, Stores!$A$2:$A$8, 0))
MATCH(…,0) = exact match → row number
Do it step by step:
- First test MATCH alone: type
=MATCH(G2, Stores!$A$2:$A$8, 0). You get a row number. - Now wrap it:
=INDEX(Stores!$E$2:$E$8, MATCH(G2, Stores!$A$2:$A$8, 0)). - Press Enter and copy down.
- Insert a new column in Stores. The formula still returns the manager.
स्टेप बाय स्टेप करून बघा:
- आधी फक्त MATCH test करा:
=MATCH(G2, Stores!$A$2:$A$8, 0)type करा. Row number मिळेल. - आता तो wrap करा:
=INDEX(Stores!$E$2:$E$8, MATCH(G2, Stores!$A$2:$A$8, 0)). - Enter दाबा आणि खाली copy करा.
- Stores मध्ये नवा column घाला. Formula तरीही manager च देतो.
स्टेप बाय स्टेप करके देखें:
- पहले सिर्फ़ MATCH test करें:
=MATCH(G2, Stores!$A$2:$A$8, 0)type करें. Row number मिलेगा. - अब उसे wrap करें:
=INDEX(Stores!$E$2:$E$8, MATCH(G2, Stores!$A$2:$A$8, 0)). - Enter दबाएँ और नीचे copy करें.
- Stores में नया column डालें. Formula फिर भी manager ही देता है.
=INDEX(array, row_num, [column_num])returns the value at a position.=MATCH(lookup_value, lookup_array, [match_type])returns the position of a value;match_type0 = exact, 1 = largest ≤ (sorted ascending), -1 = smallest ≥ (sorted descending).
Together: MATCH finds the row, INDEX returns the value.
=INDEX(Stores!$E$2:$E$8, MATCH(G2, Stores!$A$2:$A$8, 0))
| Step | Result for G2 = AMN-SLP-01 |
|---|---|
MATCH("AMN-SLP-01",Stores!$A$2:$A$8,0) |
6 |
INDEX(Stores!$E$2:$E$8,6) |
Salman |
About this data: Made-up rows so you can copy the pattern for index and match. Currencies may use ₹, £, € or $.
Left lookup (VLOOKUP can't): find the Store ID of the Area "Dharampeth":
=INDEX(Stores!$A$2:$A$8, MATCH("Dharampeth", Stores!$D$2:$D$8, 0)) → AMN-NGP-01
Advantages of INDEX + MATCH over VLOOKUP: looks left or right; inserting/deleting columns doesn't break it; you can reuse one MATCH in many INDEX formulas (faster on big data); works in every Excel version.
Ravindra Bagale's Tip
In INDEX + MATCH, many students forget the final 0 of MATCH – then the default 1 (approximate) applies and you get the wrong row on unsorted data. Also, the INDEX range and the MATCH range must start from the same row (both from row 2). Keep these two rules in mind, and INDEX MATCH is very simple.
Ravindra Bagale's Tip – मराठी
INDEX + MATCH मध्ये बरेच students MATCH चा शेवटचा 0 विसरतात – मग default 1 (approximate) लागतो आणि unsorted data वर चुकीची row येते. तसंच INDEX ची range आणि MATCH ची range एकाच row पासून सुरू व्हायला हवी (दोन्ही row 2 पासून). हे दोन नियम लक्षात ठेवा, INDEX MATCH एकदम सोपं आहे.
Ravindra Bagale's Tip – हिंदी
INDEX + MATCH में बहुत से students MATCH का आख़िरी 0 भूल जाते हैं – फिर default 1 (approximate) लगता है और unsorted data पर गलत row आती है. साथ ही INDEX की range और MATCH की range एक ही row से शुरू होनी चाहिए (दोनों row 2 से). ये दो नियम याद रखो, INDEX MATCH बहुत आसान है.
Practice task
With INDEX + MATCH: return the Platform for each Store ID; find the Store ID for the manager "Rani" (left lookup); return the Area for Store ID in cell J1.
Loading practice tasks...
- Row of a store (Easy): Find the position of "BLK-KOP-01" in A2:A8.
- Value by position (Easy): Get the 5th value of E2:E8.
- Results: position of Ravina (Easy): Find the position of "Ravina" in Students!B2:B5.
- Results: 2nd mark (Easy): Get the 2nd value of Students!D2:D5.
- INDEX and MATCH together (Medium): Find the City Manager of the store in H2.
- City of a store (Medium): Find the City of BLK-NSK-01.
- Results: marks of Salman (Medium): Find the Marks of "Salman".
- Results: class of Shahrukh (Medium): Find the Class of "Shahrukh".
- Look to the left (Hard): Find the Store ID of the manager "Raja".
- Results: roll no of the 85 scorer (Hard): Find the Roll No of the student with 85 marks.
Quick revision cards
Three standalone cards. Each one explains the idea with its own example. Scan the QR to open this lesson.
