HAPPY FRIDAY.

If you want to download the example from this blog post you can do so here to work along with.

[wpdm_package id=’9971′]

Today let’s look at a great at a couple of really flexible and powerful functions -especially together – the INDEX and the MATCH function. So what do they do?. Well they are the bigger brother of the VLOOKUP. Read on to find out why..

First let’s get some theory out of the way by looking at the syntax of the functions individually.

INDEX returns a reference to a cell based on the intersection/position of a specified row and column in a data table or an array of numbers or, to put it another way it returns an item from a specific location in a data list. The Syntax of INDEX is

=INDEX( array, row_number, column_number )

WHERE

array – a range of cells that contains your data

row_number – this is the row number or position of the cell you want to look up. Note if this is left blank or se to zero then it defaults to all rows in the array and also this is the relative position of the row in the array of cells, not the row number on the worksheet in Excel.

column_number – this is the column number or position of the cell you want to look up. Note if this is left blank or se to zero then it defaults to all columns in the array and also this is the relative position of the column in the array of cells, not the column number on the worksheet in Excel.

These Row and Column numbers cannot be BOTH left blank or zero.

Let’s take a look at an example and return the 3rd item in the 2nd column in my data set below?

I have a data table of distances between some National Parks in USA. The distance data contained in the table is a NAMED RANGE. If you want 3 great reasons to use named ranges in your formulas then you can read my previous #formulafriday blog post here.

So we can see that Excel returns 534 which is the position of the cell intersection of row 3 and column 2.

Next the let’s look at the MATCH function. MATCH can return the value of a position with in data list.

=MATCH( value, array, [match_type] )

Where

value – the value to search for in the array

array – this is the data set or range of cells that contains the value you are looking for

match_type – this is an optional argument with 3 possible values.

1 this is the default value and Excel will find the largest value that is less than or equal to value. If the match type is not entered then it defaults to 1.

0 – if this is used then Excel will find the first value that is equal to value.

-1 – is this value is used then Excel will find the smallest value that is greater than or equal to value

Again let’s take a look at example of using MATCH and use it to find the position of Death Valley in the list in Column A?.

You can see that B10 is our value, we want to find the position of, our lookup array is our list of National parks which is a nmed range of national_parks, we have used the match type 0 as we want to find an exact match. Excel tells us the Death Valley is at position 2.

WHAT NOW? – COMBINE INDEX & MATCH.

So let’s get started and combine these two functions as a very powerful alternative to VLOOKUP.

First of all here is my data set up again.

In addition to having my data table as a named range, I also have my cities for selection for the start and end destination set up as a data validation list. You can also check out my blog post here on setting up custom data lists to enable a user to select from a list of data.

So let’s get these powerful two functions working together and one HUGE advantage of the INDEX / MATCH functions is that the lookup value can be in any column in the array, unlike the VLOOKUP function, in which the lookup value must be in the first column – always, this gives great flexibility.

So in this example, let’s lookup the distance between 2x National Parks,

First we use INDEX to set the array of where our data is to be found, we then need to set the row number and column numbers to use , which are provided by the two MATCH functions. This means we can change the From and T0 cell values by using the drop down lists we used for data validtion in our data set up and the mileage will dynamicallt update. COOL HUH?.

Dont forget to sign up to the Excel at Excel Newletter for 3 free Excel tips the first Wednesday of the month. Just click on the Sign Up Form to the right or use the link below.

• Create A Micro Chart In Excel
• How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
• Create A Timestamp In Excel With Formulas
• Use TRIM for a speedy data clean up