INDEX( array, row_number, [column_number] )

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

This page provides a really good explanation – http://www.randomwok.com/excel/how-to-use-index-match/