Lookup a value based on multiple criteria

Hello,

I have a table which shows freight tariffs.
For an item I want to find the correct tariff when looking at the size and the weight of the product

In my example I have an article which is 14x10x1,5 in cm (lxwxh) with a weight of 480 gram.
Looking at the length and width the tariff in row 2 would fit. But because of the height the tariff will bump to row 3.
Then based on the weight row 8 is the one that fits all criteria. So I need to create some formula which looks at all criteria and picks the max row of one of the criteria which is the highest.

Which version of Excel are you using?

Excel 2006

I don't think there was any such version - are you sure? Is it a Mac version?

You are going to need to completely redesign your lookup table to get this lookup to work.

Attachment 688924


If redesigning is needed then redesign it is

It's not Office 2006 - it's Office 365!!! Please update your profile.

Each of your lookup tables needs to start with a lookup value of 0, and the list needs to be in increasing order - you cannot do the weights as you currently have them set up.

If you can create four separate lists for me in that format, I can tell you how then to create a formula.

Not possible to get it in increasing order. Certain weights match certain packaging size. The whole makes up the costs.
If i put the weight in in increasing order the sizes wont be in increasing order

Nothing is impossible! However, sometimes you have to make compromises.

I dare say I could work through it for you and set something up that could work, but I don't have the time needed to invest in it today. Sorry.

Well done, however I wonder how you did it. As I said, lookups need lists in ascending order. Care to share your formula for the benefit of others?