Get Mileage from Excel Lookup Table

There is a new sample file on my website, in response to a lookup question that someone asked on my Contextures Facebook page.

I'm staring at a huge spreadsheet showing the distances in miles between a few hundred job sites…Our data is accurate, but the users often enter the wrong mileage data because it's easy to make a mistake when scrolling…

How can I automate this process so that I can just enter the departure site and the arrival site and retrieve the distance between the two?

The Mileage Table

To find data in a lookup table, based on the row and column headings, you can use the INDEX and MATCH functions. Here’s the mileage lookup table in my sample file, with cities in Florida.

indexmatchmileage01

The numbers above, and to the left of the table aren’t used – they’re just there for visual verification of the formulas.

Use INDEX and MATCH

Data validation is used to create two drop down lists for city names, and an INDEX formula in the adjacent cell returns the mileage between those cities. The MATCH function finds the row for the starting city, and the column for the destination city.

Here’s the formula that returns the mileage:

=INDEX(G3:P12,MATCH(A3,F3:F12,0),MATCH(B3,G2:P2,0))

indexmatchmileage02

Highlight the Mileage for Selected Cities

As an extra way to verify the results, I’ve added conditional formatting in the lookup table, to highlight the cell with the mileage for the selected cities.

indexmatchmileage03

Here is the conditional formatting formula:

=AND($F3=$A$3,G$2=$B$3)

Download the Sample File

To see the formulas and the conditional formatting, you can download the sample file from my website. On the Sample Files page, look for FN0026 – Get Travel Distance from Mileage Chart

The file is zipped, and in xlsx format. There are no macros in the file.

Watch the Video

To see the steps for creating the lookup formula, please watch this short video.

_

_____________________

1 comment to Get Mileage from Excel Lookup Table

Leave a Reply

  

  

  

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>