Let’s have some INDEX() and MATCH() fun in our Lookup function series.
by David Goodmanson
On their own, the INDEX() and MATCH() functions are fairly straightforward but when they are combined they create a very powerful Lookup tool. Both functions have 3 parts to their arguments.
INDEX(range, row_number, column_number)
and
MATCH(lookup_value, range, match_type)
Let’s use each function individually in the table of distances shown below.
Figure 1 – table of distances from http://www.hm-usa.com/distance/ca.html
For example,
=MATCH(A7, A2:A12, 0) returns 6, meaning that in A2 to A12 in column A the cell A7 (Needles) is the 6th row in the range. The zero in the formula denotes an exact match is to be found.
=INDEX(A1:L12,4,10) returns 290. How does that work? The first argument in INDEX() is the range or data array that we want to lookup. The 2nd and 3rd numbers are the row and column offsets. Easy right? In this article we will only use MATCH() and INDEX() in the way just specified (more to come in the future).
OK, so far so good. Now the one thing we don’t want to do is have to meticulously put in the actual cell references to find out what the distance is between 2 California cities, we want Excel to do the work. So, let’s use MATCH() and INDEX() together to make an Excel application.
Pick from a list
Before we go any further let’s make it easier to choose the cities. You could type in the two city names but just one letter wrong will make the whole thing fall apart. Here’s how to make a pull-down list based on the list of cities in the Excel worksheet.
We are going to use Data Validation to put in a combo box on the worksheet to give us a list to select a City From, and a list to select a Destination City. So if you have copied the data from the hyperlink shown at figure 1 into a worksheet, that will be the first task.
Next, select a cell near the data table where we will put the list of Cities From. Then, in your menu, go to Data | Validation and you’ll get an input box like this:
Figure 2 – Data Validation Input
On my worksheet I have the list of “From” cities in cells C7:C17. In the Input Message tab of the box put the word From in the input message section. This will display the message From when the mouse hovers over it. Click OK, and select the cell underneath. In the same way enter the “To” cities range. On my application it is D6:N6.
Back to the lookup …
Finally, skip one cell down from the “To” range to the next cell and we will enter the magic lookup formula which will drive the application.
=INDEX(D7:N17,MATCH(C2,C7:C17,0),MATCH(C3,D6:N6,0))
OK, so how does this work? Well remember, INDEX() uses three arguments in its syntax. 1) Array or range of data, which in this case is D7:N17; this is the data table which returns the answers to “How far is it from Barstow to LA? 2) is the row reference which MATCH() returns and 3) is the column reference which MATCH() returns also.
So the 2nd part of INDEX() is MATCH(C2, C7:C17,0). C2 is the list box we created using Data Validation. If we select Barstow then MATCH() will look in cells C7:C17 in our table to find an exact match to Barstow. It finds Barstow in row 1 of our nominated range. So 1 becomes the 2nd argument of INDEX(). Similarly MATCH() returns Los Angeles in the 3rd part of INDEX().
You can adapt this model to any grid or table of data and use INDEX() and MATCH() interactively to build a lookup model superior to Vlookup.
Figure 3 – Distance Lookup Application
See Also
- Some Excel worksheets should not be shared
- Excel’s IF function
- Calculating with Vlookup
- Lookup from a list in Excel
