# Blog Archives

## Excel – Using INDEX & MATCH (2)

You’ve probably come across the **MIN** and **MAX** functions which give you the lowest and highest values, respectively, in a range of cells. This is fine as long as all you need to know is the lowest or highest figure, but what if you want to know *who* or *what* got the lowest/highest figure?

Let’s take a simple example;

So we have a list of people and the amounts they have spent. With a quick scan of the data you can see that Mrs Delores Turner had the lowest spend, and Mr Louie Moreno had the highest. This is easy because there is only a very small amount of data, but if there were hundreds or even thousands of lines it would not be so obvious.

By using the **MIN** and **MAX** functions I can quickly find the lowest and highest values using **MIN(B2:B11)** or **MAX(B2:B11)**, but it doesn’t tell me “who” spent these values.

So how can you get around this?

This is where **INDEX** and **MATCH** come in. If you are unfamiliar with these two functions, check my blog https://excelmate.wordpress.com/2014/12/04/excel-using-index-match-part-1 which will show you how they work. For the rest of this blog I will assume you have either read the blog or are already familiar with **INDEX** and **MATCH**.

To get the name of the person with the lowest/highest spend I need the following formula;

**=INDEX(A2:A11,MATCH(MIN(B2:B11),B2:B11,0)**

The **MATCH** part of the formula find finds the lowest value in cells **B2:B11**. It then uses this number and finds its position within the range. In this example the **MATCH** function will return the value 4 i.e. it is the 4^{th} value in the selected column of data. The index function then looks within range **A2:A11** and looks in the 4^{th} row – the value returned by **MATCH**. You don’t need to reference the column because there is only one column selected in the array portion of the **INDEX** function.

The find the person with the highest spend, the formula would be;

**=INDEX(A2:A11,MATCH(MAX(B2:B11),B2:B11,0)**

So as you can see **INDEX** and **MATCH**, although pretty useless on their own as we saw in the earlier blog, when combined they are a useful duet of functions to know and should be part of your “must know” functions in Excel.

## Excel – Using INDEX & MATCH (Part 1)

If you are familiar with the **VLOOKUP** and **HLOOKUP** functions then you will know how useful they can be. There is however one drawback in that they only search for the look up value in the first column or row of a selected look up table. In the majority of cases this is fine, but there may be times when you need to look both to the right and left of the look up column.

This is where **INDEX** and **MATCH** functions come in. On their own, they are probably two of the most useless functions going – together they are extremely powerful and sometimes jokily referred to as VLOOKUP on steroids! But to understand how they work we need to take at look at each one in turn.

MATCH

The basic syntax for **MATCH** is:

**=MATCH(lookup_value,lookup_array, [match type])**

Which in plain English is…

**=MATCH(what value are you looking for, where are you trying to find it, do you want a near or exact match)**

Whereas **V/HLOOKUP** require a **TRUE** or **FALSE** (1 of 0) value in the final argument, **MATCH** has three options;

1 = nearest match under/less than the look up value

0 = exact match

-1 = nearest match over/greater than the look up value

**MATCH** basically returns the position (represented by a number) of the look up value in a single row or column.

If I am looking for “X” in a row of cells then my formula would be =MATCH( “X”,A1:F1,0)

My function would return the number 4 i.e. it is in the 4^{th} cell, reading from left to right in my selected cells. If looking in a single column of cells then it returns the position reading from top to bottom. Not the most useful bit of information you are ever likely to extract from a spreadsheet if the truth be told!

INDEX

There are two versions of **INDEX**, but the one I am looking at here is the basic one that only looks at one table. The syntax for **INDEX** is:

=INDEX(array, row_num,[column_number])

…or in plain English;

=INDEX(range to check, which row do you want to look in, which column do you want to look in)

Using **INDEX** on its own is not the most useful thing you can do in Excel. For example;

=INDEX(A1:D7,5,3) would return 32

I could have just as easily written =C5. So it looks like a very long winded way of referencing cells. However…when you combine **MATCH** and **INDEX** together you get a completely different beast.

I’ll use a table we use in class as it is good way of showing how it can be used.

As you can see, we have a number of towns/cities around the UK with distances between them. Rather than search myself for distances between two given locations, I want to be able to use a series of drop down lists to select locations and a formula to find the correct distance between them. So I need something dynamic to do this, and this is where **INDEX** and **MATCH** come into play.

I’m going to use a couple of drop down lists to make my choices easier rather than type out the names each time. I’ll assume you know how to create **VALIDATION** lists, if not please see my blog http://wp.me/p2EAVc-5b on how to set those up.

To break this down I will use the **MATCH** function on its own to return the row and column numbers for each town/city I choose from my drop down lists. First, calculate the row position;

**=MATCH(S19,A1:A17,0)**

In this example, I have chosen Southampton and the **MATCH** function returns the number 5.

Now for the column position;

**=MATCH(S20,A1:Q1,0)**

This time I’ve selected Nottingham and therefore returns the number 10.

Change the location in each of the drop down lists and see the values from each **MATCH** function change. So now you can see that **MATCH** is a dynamic function when linked to something like a **VALIDATION** list.

As we saw before, **INDEX** relies on row and column numbers to work. Normally, these would be manually entered and therefore fixed. As **MATCH** can be dynamic, it returns a number we can use to our advantage by embedding a **MATCH** function in the row and column arguments of **INDEX**.

Our new nested function will look like this;

**=INDEX(A1:Q17,MATCH(S19,A1:A17,0),MATCH(S20,A1:Q1,0))**

Although it looks complicated, and may take a while to get used to the nested syntax, it is basically an **INDEX** function with two **MATCH** functions sitting inside it.

This now gives you a totally dynamic search function. Although in this example the look up values are the top row and first column, **INDEX & MATCH** will work using the look up on any column or row in your table.

What’s really neat, is that if you decide to move the town names around a bit (sticking to the same column or row mind you!) it will still work because the **MATCH** function will look for an exact match of the text value chosen in the drop down. Unless the spelling changes, it will find it wherever it is in the row or column!

In Part 2 I’ll show you how you can use **INDEX** and **MATCH** across multiple tables as well as extracting information from one column based on data found in another.