How To: Writing Index, Match and Index, Match, Match formulas
Writing a formula in Excel can be tricky, especially if you plan on writing one that has a bit of length to it and the INDEX and MATCH functions are no exception. There are a couple of things to keep in mind when writing formulas that will make it much simpler.
Consider the following data:
Suppose we wanted to write a formula to look up specific values in this data. We know the lot number that we want to look up (60) and we know we want the data in Column D, the Rate/Day, for that lot number. To begin, work from the inside of the formula, outward. For writing an Index, Match formula you start with the inner-most piece of the formula, the MATCH function.
=MATCH(60, A2:A13,0)
This returns 2. Why? Because we are asking Excel to look in Rows A2 through A13 and return the POSITION NUMBER of lot number 60. In this case we are returning position 2 because lot 60 is in cell A3. Cell A2 is where we told the MATCH function to start looking, so A2 would be position 1 and A3 would be position 2. The zero at the end specifies that we want an exact match of our data.
Okay so now we have the position number of lot 60 in our data. We know we want to return the rate/day for that lot number. That means we want position 4 in our data. Say what??? How did I get 4? Let me explain - when we were looking up the lot number we were looking at rows, or up and down the data. Now we want to look at columns, or left to right in the data. If we start with the lot number being position 1, the location being position 2, etc. we end up with the rate/day being position 4, horizontally. We already have our MATCH formula written so it's time to plug it into our INDEX formula.
=INDEX(A2:E13, [Vertical Position], [Horizontal Position])
We want to include the entire array of data so we use A2:E13. Next, we want to plug in our MATCH formula to the vertical position area of the formula. Why? Because when we did a MATCH formula we were looking at rows, up and down, vertically, for lot 60. Remember now? So our formula now looks like this:
=INDEX(A2:E13, MATCH(60, A2:A13,0), [Horizontal Position])
All that's left is the horizontal position number. We know we want the Rate/Day which is position 4 in our columns of data. So we add it to our overall formula:
=INDEX(A2:E13, MATCH(60, A2:A13,0), 4)
There we go! The formula returns 3.95
Okay let's make this a bit more interesting. What if we didn't know where the Rate/Day was located in our data? In other words, what if we didn't know Rate/Day was in position 4? What if the data was prone to changing and Rate/Day could be in position 2 or position 3? We would need a formula to tell us where Rate/Day is located. How can we do this? You guessed it - with the MATCH function. Instead of looking at lot numbers, however, we would look at the column headers.
=MATCH("Rate/Day", A1:E1,0)
This tells MATCH we want to find Rate/Day horizontally because we are looking from A1 to E1. The zero on the end says we want to match Rate/Day exactly. If we just run this formula we return 4. Now lets put it all together. Again here is our basic formula:
=INDEX(A2:E13, [Vertical Position], [Horizontal Position])
we know we want lot 60:
=INDEX(A2:E13, MATCH(60, A2:A13,0), [Horizontal Position])
we know we want the rate/day for lot 60:
=INDEX(A2:E13, MATCH(60, A2:A13,0), MATCH("Rate/Day", A1:E1,0))
Again, we return 3.95
Congratulations! You've just written your first INDEX, MATCH, MATCH function and understood how it works!