Lookups

[ChristineRutter]

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/

 

[menuges]

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]

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]

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
	A	B	C	D
1	Sheet2(updatedwheneveranewFrom-Tooccurs)
2	From	To	Concat	Mileage
3	Southend	Chelsea	SouthendChelsea	48
4	Dixboro	Milan	DixboroMilan	54
5	Rochester	Newbury	RochesterNewbury	14
6	Southend	Newbury	SouthendNewbury	20
7
8	Sheet1(tracksmileage)
9	From	To	Concat	Mileage
10	Southend	Chelsea	SouthendChelsea	48
11	Dixboro	Milan	DixboroMilan	54
12	Rochester	Newbury	RochesterNewbury	14
13	Rochester	NewHaven	RochesterNewHaven	#N/A
14	Dixboro	Milan	DixboroMilan	54
15	Rochester	Newbury	RochesterNewbury	14
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]

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
	A	B	C	D	E	F	G
1		Chelsea	Dixboro	Milan	Newbury	Rochester	Southend
2	Chelsea	x	10	15	12	4	8
3	Dixboro		x	14	3	8	7
4	Milan			x	60	42	18
5	Newbury				x	23	5
6	Rochester					x	17
7	Southend						x
8
9			From	To	Index1	Index2	Total
10			Chelsea	Newbury	12	0	12
11			Southend	Milan	0	18	18
12			Dixboro	Southend	7	0	7
13			Chelsea	Dixboro	10	0	10
14			Chelsea	Rockford	#N/A	#N/A	#N/A
Sheet1

 

[xenou]

Menuges...I think we are on to the same solution

 

[ChristineRutter]

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.