INDEX and MATCH practice in Google Sheets
MATCH finds the position of a value and INDEX returns the value at a position. Both take the same arguments in Google Sheets as in Excel, so INDEX MATCH looks up in any direction there too.
How INDEX and MATCH work together
MATCH finds where a value sits in a list and returns that position as a number. INDEX does the opposite: you give it a range and a position, and it returns the value there. MATCH takes the value to find, the range to search and 0 for an exact match. INDEX takes the range to return from and a row number. Both work the same in Excel and Google Sheets.
=INDEX(C2:C7,MATCH(E2,A2:A7,0)) finds the product from E2 in A2:A7 and returns the price from the same row in C2:C7. The two ranges are separate, so the answer can sit left of the lookup column. =INDEX(B2:E5,MATCH(H1,A2:A5,0),MATCH(H2,B1:E1,0)) is a two-way lookup: one MATCH picks the row and the other picks the column.
The classic mistake is forgetting the 0 in MATCH. Then it looks for the nearest smaller value and can return a wrong row without any error. Start both ranges on the same row, or the position points to the wrong cell. XLOOKUP needs less typing, but INDEX and MATCH works in every version. The exercises below cover finding a position, a price lookup, looking to the left and a two-way lookup.
- 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)).
Functions in this topic
Questions
Should I use INDEX MATCH or XLOOKUP in Google Sheets?
Both work. XLOOKUP is shorter to write; INDEX MATCH also works in older versions of Excel, so it is worth knowing if you share files.
Can I use whole columns like A:A in INDEX MATCH in Google Sheets?
Yes. =INDEX(C:C,MATCH(E2,A:A,0)) works in Google Sheets and picks up new rows automatically, because both ranges cover the whole column. Use whole columns for both ranges so the positions line up. Whole-column references work the same way in Excel.