# Excel – Index and Match

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. INDEX formula highlighted Specify where to look – in this case between cells G2 and G6 Specify the cell value to return by referencing the column and row, within your lookup array/area. In this case the 3rd row down and the 1st column within the look up area (G2 to G6) MATCH

`MATCH( value, array, [match_type] )`
The Match function returns a number (not a value like the INDEX function does)
In the syntax –
Value is what you want to search for in a list or a table of data
Array is the table or list of data that you want to search in
Match type – this is optional:
1 – this is default method of matching.  It will find a match that is less than or equal to the lookup value.  Lookup table/list  should be sorted in ascending order first (easy enough to do with the data tab)
0  – it will find the first value that is equal to the lookup value – you’ll want to use this most of the time!
-1 – will find the smallest value that is greater than or equal to the lookup value. Data list or table should be sorted in descending order first.
The MATCH function is not arsed whether your lookup value is upper or lower case; it returns n/a errors if nothing is found.

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.

Index1Match_Example

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″ ))

SEOExcel Spreadsheet has some data you can play around with

Another (better) tutorial here:

How to Use INDEX MATCH MATCH

“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 ) )