Lookups

ChristineRutter

New Member
Joined
Aug 12, 2002
Messages
16
Hi, I wonder if anyone can help me.

I'm trying to create a mileage spreadsheet for my husband.

In col 1 is the from location, col 2 is the to location and col 3 is the mileage between the 2 locations.

What I want to do is lookup this mileage from another sheet which is set up the same way, but includes the mileage see eg below

Sheet 1

Col1 Col2 Col3
Southend Chelsea ????



Sheet 2

Col1 Col2 Col3
Southend Chelsea 48

The lookup sheet will have lots of froms and tos and will be updated if a trip wasn't on there before.

How can I do this please?

Thanks/
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

menuges

New Member
Joined
Aug 30, 2007
Messages
5
Hi,

I'd insert a third column in column 1 of both sheets. The new column would be a unique (non-repeating) reference to each of your mileage record and would then double as a lookup value to extract the actual mileage from your sheets 2 using Vlookup. Your unique reference could be a simple concatenation of values in columns 2 and 3 eg SouthendChelsea.

Rgds
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Make a table with the from locations down the left, the to locations across the top and the mileage at the intersection. Then you will be able to use a combination of VLOOKUP/HLOOKUP or INDEX/MATCH.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
I think this will work for you. I've added a column to concatenate the cities and create a single value for the lookup. This provides the unique lookup value - you also have the option of leaving it hidden most of the time.

On the second sheet, if the two cities cannot be matched on their concatenated value, you'd know you need to update the first sheet.
Book1
ABCD
1Sheet2(updatedwheneveranewFrom-Tooccurs)
2FromToConcatMileage
3SouthendChelseaSouthendChelsea48
4DixboroMilanDixboroMilan54
5RochesterNewburyRochesterNewbury14
6SouthendNewburySouthendNewbury20
7
8Sheet1(tracksmileage)
9FromToConcatMileage
10SouthendChelseaSouthendChelsea48
11DixboroMilanDixboroMilan54
12RochesterNewburyRochesterNewbury14
13RochesterNewHavenRochesterNewHaven#N/A
14DixboroMilanDixboroMilan54
15RochesterNewburyRochesterNewbury14
Sheet1



FYI a trick to maintaining the range properly for the lookup is to never add new cities "at the bottom" but to insert a row inside the table (second from the bottom, or just anywhere between the first and last rows of the data) - you may be sorting this by city but in that case on those occasions you need to add a row to the bottom, just insert a row above the last row, add the cities, then cut the old last row and move before the new last row. What happens if you are not careful is that your lookup range will not include the new additions.

Another idea, of course, is to use a larger range for your data that includes extra blank rows at the bottom that you can just fill in as needed.
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

Andrew, I like your idea so much I had to try it...

I took the approach of not repeating any data in the lookup table, so in my mileage chart I first check the distance from the cities in From-To order, and if that doesn't return a value I check the distance in To-From order (i.e., Dixboro-Newbury is listed in my table, but Newbury-Dixboro is not). Well, this makes for a prettier picture...

In my mileage chart, I also created a check on the sum, so that if by mistake I had a value listed in both directions, it would alert me.

Is this still improveable? Actually, I think we have several solutions going now... . Regards.
20070901_MileageLookup.xls
ABCDEFG
1ChelseaDixboroMilanNewburyRochesterSouthend
2Chelseax10151248
3Dixborox14387
4Milanx604218
5Newburyx235
6Rochesterx17
7Southendx
8
9FromToIndex1Index2Total
10ChelseaNewbury12012
11SouthendMilan01818
12DixboroSouthend707
13ChelseaDixboro10010
14ChelseaRockford#N/A#N/A#N/A
Sheet1
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,795
Office Version
  1. 2019
Platform
  1. Windows
Menuges...I think we are on to the same solution
(y)
 

ChristineRutter

New Member
Joined
Aug 12, 2002
Messages
16
Thanks

You guys are just awsome.

I think I am going with the concat suggestion, as the other looks a little too complex for me at this stage.

I really do appreciate your help.

Thanks again.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,915
Messages
5,598,850
Members
414,263
Latest member
sherrcha

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top