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
INDEX + MATCH madhe khup students MATCH cha shevtcha 0 visartat – mag default 1 (approximate) lagto aani unsorted data var chukicha row yeto. Tasech INDEX chi range aani MATCH chi range same row pasun suru vhayla havi (doghi row 2 pasun). He don niyam lakshat theva, INDEX MATCH ekdum simple aahe.
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.