Lookup by multiple criteria

Hi

I've been trying to construct a formula to lookup values in a table based on another set of criteria

What I have is a table of rates each line relates to a different place in the world designated by a 3 letter destination code and relates to breakpoint by weight

I'm trying to write a formula that will lookup the 3 letter code in the first column and then look in the horizontal row at the appropriate weight breakpoint.

heres an idea of how the sheet looks at present

Destination Airport Code Min -50 50 100

Abu Dhabi AUH DH 5.00 0.25 0.25 0.25
Accra ACC GH 20.00 1.15 1.15 1.10
Adelaide ADL AU 10.00 1.60 1.60 1.30
Atlanta ATL US 5.00 0.70 0.70 0.68
Auckland AKL NZ 10.00 1.25 1.25 1.20
Bahrain BAH BH 15.00 0.85 0.85 0.65


So what I'm trying to do is to create a drop down list of the airport codes and then enter a weight, lets say 45 kgs and then have a formula retrieve the corresponding rate so 45kgs would be under 50 kgs to say ADL (Adelaide) which would return a value of 1.60.

I think I need to use the IF command to determine the colmum along with either Vloopkup or hlookup to determne the rate but I'm draawing a blank, if anyone can help I'd be really grateful

Thanks

Ian

Originally Posted by teylyn

where is the connection between 45 kgs and Adelaide's value of 1.60?

there's one element missing here. Your first row maybe needs to show the weight categories. What are they? This would all be easier if you posted your data in a workbook instead of pasting it in a post.

Saves a heck of a lot of time!

Edit: trying to interpret your data, I've come up with this. See what you can do with it

cheers

Thanks so much I think you have the idea, I've attached the complete sheet so you can see, if you look at the second tab entitled quote screen what I'm trying to do is create a drop down list and then this looks up the corresponding rate based on the weight breakpoint to return the appropraite rate so I can use this for my other caluculations.

Many Many thanks !

Ian

Originally Posted by teylyn

explain to me:

Please Login or Register  to view this content.

what is "-50" in row 3? Can a piece of freight have negative weight?

The blank rows in your data column will be a problem. You need unique codes in column A or in column C.

Then fix row 3 to be meaningful steps of weight categories and just adapt the formula I posted earlier to reflect the rows and columns you are using right now.

cheers.

Just wanted to say thanks, amanaged to adapt your formula, still not sure how it works but very grateful for your help

Thanks

ian