Freigeben über

How To: Writing Index, Match and Index, Match, Match formulas

  • Artikel

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!

Comments

  • Anonymous
    January 01, 2003
    Made perfect Sense. thanks!!

  • Anonymous
    January 01, 2003
    Geat! thanks

  • Anonymous
    June 07, 2013
    This was very informative, and will be benificial, however, it did not provide the results I was looking for.  The search continues.

  • Anonymous
    August 16, 2013
    Very helpful & easy way  to understand index and match

  • Anonymous
    September 08, 2013
    I have yet to see an example of incorporating a lookup value to another outside of the table. Working inside a table is easy going outside for retrieval of data to apply to a calculated column I can find no example. Can some one tackle the harder stuff.

  • Anonymous
    November 21, 2013
    Brilliant - exactly what I needed and explained fully for understanding too.  Good work :-)

  • Anonymous
    March 03, 2014
    This was very informative. I needed to learn how to use index and match, and now I know how.

    Thanks

  • Anonymous
    August 08, 2014
    I'm having trouble getting this to work using arrays formatted as tables. Will the MATCH function not work if your horizontal array is [#HEADERS]?

  • Anonymous
    August 19, 2014
    Perfect! Solved my confusion in less than 15 minutes.

  • Anonymous
    October 02, 2014
    Good explaination! Thank you!