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.

Start the first exercise

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.

  1. 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.
  2. 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.
  3. 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.
  4. 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

Read the guide

Questions

How do I use INDEX MATCH with multiple criteria?

In Excel 365, multiply the tests inside MATCH and look for 1: =INDEX(C2:C20,MATCH(1,(A2:A20=F1)*(B2:B20=F2),0)). The product is 1 only on rows where both tests are true. In older versions of Excel, confirm the formula with Ctrl+Shift+Enter so it runs as an array formula.

Is INDEX MATCH better than VLOOKUP?

For most jobs, yes. INDEX and MATCH can look to the left, and the formula does not break when you insert a column in the table, because you point at whole columns instead of counting a column number. VLOOKUP is shorter to write and fine for a simple lookup to the right.

How do I return a whole row with INDEX MATCH?

Set the column argument of INDEX to 0: =INDEX(A2:E20,MATCH(G1,A2:A20,0),0) returns the entire matching row. In Excel 365 the row spills into the cells to the right. In older versions it only works inside another function, such as SUM, or as an array formula.