Two Criteria - Two lookups in one...

[silly simon]

Hi all,

I'm trying not use the concatenate formula to achieve this goal.

I have two sheets of data, set out as follows.

Sheet 1 contains data that needs to have a cost attributed to it, depending on two variables.

• Place (A:A)_____Size (B:B)____Cost (C:C)
  Bedford_________1/2 Page______?
  Cambridge_______3 Pages________?

I need to add the cost from a seperate sheet, which is laid out thus...

• Place_____1/2 Page____1 Page_____2 Pages_____3 Pages
  Bedford______£310______£415______£560_______£670
  Cambridge______£285_____£370______£460______£580

What formula would i need to use, to populate the cell where the ? is, i.e £310.

Thanks in advance for any help

simon

 

[barry houdini]

Welcome to the board

Assuming your first table has each combination only once here's one way.....

assuming your second table starts at A1 then in B2 use

=SUMPRODUCT(--($A2=sheet1!$A$1:$A$100),--(B$1=sheet1!$B$1:$B$100),sheet1!$C$1:$C$100)

copied across and down

 

[silly simon]

Thanks this helps,

but it is the wrong way round!

What I ideally need to do is take the data from the rows to populate the top, where the ? are, with the formula given it works in reverse.

Do you know how to reverse this process?

 

[barry houdini]

OK, sorry, I see what you mean. Then assuming the same setup in C2 sheet1 use

=INDEX(sheet2!B$2:K$10,MATCH(A2,sheet2!A$2:A$10,0),MATCH(B2,sheet2!B$1:K$1,0))

copied down

adjust the ranges depending on the extent of your table in sheet2

 

[silly simon]

Barry,

Thanks very much, it is much appreciated.

You are a true magician!

simon. x