Range Lookup

[christoph404]

Hi,

I'm hoping for some support for the following problem. I simplified
the problem for the sake of this group. So, yes, I know the data
doesn't really make any sense.

I have a table with the following fields:

<Country> <Transportation> <Start Mile> <End Mile> <Rate p. Mile>

So, as an example, here are a few entries in the table:

Country Transp. Start Mile End Mile Rate
France Train 0 6 10.00
France Train 7 11 11.00
France Train 12 9999 12.00
France Car 0 20 13.00
France Car 21 9999 14.00
France Air 0 20 15.00
France Air 21 50 16.00
France Air 51 9999 17.00
UK Train 0 6 18.00
UK Train 7 11 19.00
UK Train 12 9999 20.00
UK Car 0 20 21.00
UK Car 21 9999 22.00
UK Air 0 20 23.00
UK Air 21 50 24.00
UK Air 51 9999 25.00

I know that this is more of a database task but I am unfortunately
bound to Excel. How can I go into this table and look up a <Rate>
value based on a given Country, Transportation and Mileage?

I converted the above to a pivot table but I'm still stuck that the
mileage is a range - from starting miles to end miles. I'd like to go
into the table above and say, look up the rate for "UK", "Air" with
200 Miles. Is that possible in Excel?

Thanks much,
Christoph

 

[christoph404]

A few suggestions:

1. make one new code column, to the very left, joining Country, transp
and start mile. Start mile should be formatted as a text with leading
zeros making numbers of digits equal. example

FranceTrain0000
FranceTrain0007
FranceTrain0012
Francecar0000
Francecar0006

then you can use vlookup() agains this code column. Ex:
vlookup(A2&B2&C2,A4:E100;4;1)

given desired country=A2, transp = B2 and start=c2 (formatted like 0006 etc)

I think this could work...?

2. Another solution could be use of array formulas, if this is familiar
to you?

Thank you for your reply. I don't think that (1) will work because it
would only return a rate with a vlookup() if the startmile is exactly
the mail I am looking for. What if the mile I'm looking for is one or
two larger than the start mile? For example, in the table I would have
FranceCar0006 but I need to find the lookup value not for 6 but for 8?

I do think this can only be done with arrays...

Christoph

 

[Herbert Seidenberg]

Excel 2007
With Sumifs() and drop-down list selection.
http://c0444202.cdn.cloudfiles.rackspacecloud.com/01_13_10a.xlsm