# Monthly Archives: December 2014

## 2014 in review

The WordPress.com stats helper monkeys prepared a 2014 annual report for this blog.

Here’s an excerpt:

The concert hall at the Sydney Opera House holds 2,700 people. This blog was viewed about

57,000times in 2014. If it were a concert at Sydney Opera House, it would take about 21 sold-out performances for that many people to see it.

Click here to see the complete report.

## 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.