There is a new sample file on my website, in response to a lookup question that someone asked on my Contextures Facebook page. The sample file shows how to get mileage from Excel lookup table, when you pick two cities.
Here is the question from the 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.
NOTE: 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
In the sample file, data validation is used to create two drop down lists for city names, in columns A and B. In column C, an INDEX formula returns the mileage between the two selected cities.
The MATCH function is used twice in the formula, to find:
- the row for the starting city,
- 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))
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.
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, download the Get Mileage from Excel Lookup Table 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, watch this short video – Get Mileage from Excel Lookup Table.
_____________________
I like the formula but how can i generate the chart for a number of towns in UK
Do you have an easy way to generate the mileage chart? Where I work, we create a mileage chart by manually looking up the driving distance and time between clients. It would be nice to be able to enter an address and have the chart update automatically.
Very helpful! I knew there was a way to do this but I haven’t been using Excel that much lately and I just couldn’t picture the solution. I use VLOOKUP all the time but not INDEX. Thank you for publishing this and sharing your knowledge with the world! 🙂
How can I do this with VLOOKUP (if possible)?
Thank you, this was an excellent video. It did exactly what I needed and has made my mileage documentation a hundred times faster and easier.
How can you do this for more than one instance. For example, I want to use this to populate mileage report and I need to do this for multiple lines. Is there a way to do it so I don’t have to manually recreate for each line?