4.4 INDEX and MATCH
=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 |
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.