INDEX and MATCH practice exercises
MATCH finds the position of a value; INDEX returns the value at a position. Together they look up in any direction, also to the left.
- 1Find a position with MATCHIn E2, use MATCH to find the position of the product in D2 within A2:A7. Add 0 as the last argument for an exact match.
- 2Look up a price with INDEX and MATCHPremiumIn F2, return the Price of the product in E2. Use MATCH to find its row in A2:A7 and INDEX to pick that row from C2:C7.
- 3Look to the left with INDEX and MATCHPremiumThe IDs are in column C, to the right of the names. In F2, return the Name for the ID in E2 with INDEX and MATCH.
- 4A two-way lookup with INDEX and two MATCHesPremiumIn H3, find the sales for the product in H1 and the region in H2. INDEX the numbers B2:E5 with one MATCH for the row and one for the column: INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0)).