# Two Criteria - Two lookups in one...

#### silly simon

##### New Member
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

### Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).
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

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?

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

Barry,

Thanks very much, it is much appreciated.

You are a true magician!

simon. x

Replies
3
Views
203
Replies
23
Views
340
Replies
10
Views
597
Replies
3
Views
208
Replies
8
Views
617

1,219,569
Messages
6,149,042
Members
450,853
Latest member
xtiinctt

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back