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 in excel seo
INDEX formula highlighted
index excel
Specify where to look – in this case between cells G2 and G6
Index excel screenshot
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)

index excel

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.
Match function excel 2010
MATCH Function

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:

Named Range in Excel 2010
Named Range

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 and match in excel 2010
INDEX & MATCH

INDEX & MATCH with 2 MATCHES

This gets more complicated, I stole this example from the video below:

2 way index and match

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:

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s