INDEX & MATCH is one of several “lookup” functions used in Excel 2010
INDEX( array, row_number, [column_number] )
Everyone loves VLOOKUP but you can’t look to a column in the left to retrieve data. You can always copy and paste
INDEX seems a bit poo used on its own, it just returns whatever value is in a cell which you specify by giving the formula the table co-ordinates/array and how many rows down the cell is within the specified table, and how many columns across.
MATCH( value, array, [match_type] )
Value is what you want to search for in a list or a table of data
0 – it will find the first value that is equal to the lookup value – you’ll want to use this most of the time!
INDEX & MATCH with 1 MATCH
Basically, you stick the MATCH function inside the INDEX function
Let’s say you want to find the tax rate that an MMA fighter pays according to his wages
To start of you should name your ranges, just highlight the different columns or tables of data and give them a name by typing in the box on the top left hand side:
Start off by typing naming the ranges as “taxrate”, “state” and “wages”
We are looking to return the taxrate somebody pays in a certain state on a certain wage
The formula can be seen in the image below.
It’s pretty easy. Start off with an INDEX function, then type the named range or coordinates of the lookup data. This is the data which contains a value which you want to return.
In this example we want to return a taxrate
The type a comma and then MATCH
Next type in or select a cell that contains what you want to match! In this example, we want to find what tax rate someone earning 20,000 pays.
INDEX & MATCH with 2 MATCHES
This gets more complicated, I stole this example from the video below:
So the first range B9 to G17 is where we are looking to return something from
We find B4’s value in column A of the table (or approximate in this case, because of the -1 in the formula)
Then We find B3’s value in Row 8 of the table.
These 2 matches act as co-ordinates, to match and return 33,300
Remember it like this:
=INDEX ( Column to get a return value from , ( MATCH ( Cell Ref of Lookup Value , Column I want to find the lookup value in , Enter “0″ ))
This page provides a really good explanation – http://www.randomwok.com/excel/how-to-use-index-match/
SEOExcel Spreadsheet has some data you can play around with
Another (better) tutorial here:
“A matrix lookup can only work if your data table has lookup values on both the top and left hand side.”
= INDEX ( entire data to look in, MATCH ( vertical lookup value, entire left hand lookup column , 0 ) , MATCH ( horizontal lookup value , entire top header row , 0 ) )