Lookup 2 data points then into a table to find the the relevant cell

[king10001]

Hi

Trying to lookup with 2 variables

Countries and Weight which should give me a cost from a table.

The countries are split into zones "Zone Data" tab

The "Info table" holds my cost associated to the specific zones against weight

"Output" is where I want to choose a country and a weight which will look the the specific zones and the specific weights and bring out correct price.

[Richard Buttrey]

Hi,

One way in D5

Please Login or Register  to view this content.

But personally I'd change your basic data layout into a format that can be used by a Pivot Table and use a PT. with two Slicers to seelect Country and weight - See attached

[king10001]

Hi

Ok never used pivot tables before guess Id better learn then.

[Richard Buttrey]

Hi

Ok never used pivot tables before guess Id better learn then.

I agree. It will pay immense dividends for tasks like reporting. Given you have a table of data in the right layout PTs take take a minute or two to set up. The big advantage is that you can drive out statistics like totals, counts, max/min etc., which would otherwise need Excel compound formulae/functions, with a few selections from drop downs.

Thanks for the rep.

[king10001]

Hi

I had a look at pivot tables.

In this case I cant seem to get over a weight of 12.

[Richard Buttrey]

Hi

I had a look at pivot tables.

In this case I cant seem to get over a weight of 12.

That's because the last MATCH function was referring to column A not Row 1
change to:

Please Login or Register  to view this content.

However I can't urge you enough to check out PTs and use the PT approach here. Over your time with Excel you will save yourself enormous amounts of time and effort.

[king10001]

I will do, need to get this done today asap so not much time to learn it.