Here it is:
car bus eat walk
box cat rug talk
apple pear fruit 5 10 15 20
apple corn fruit 7 3 2 4

I need to on another page do the following:
If D1=car and D2=box AND A4=apple and B4=pear and C4=fruit, bring back "5"
Can anyone help? Thanks

Assuming that Sheet2 contains your source data, try...

=SUMPRODUCT((Sheet2!$D$3:$G$4)*(Sheet2!$D$1:$G$1=D $1)*(Sheet2!$D$2:$G$2=D
$2)*(Sheet2!$A$3:$A$4=$A4)*(Sheet2!$B$3:$B$4=$B4)* (Sheet2!$C$3:$C$4=$C4))

Hope this helps!

In article ,
"Deeds" wrote:

Here it is:
car bus eat walk
box cat rug talk
apple pear fruit 5 10 15 20
apple corn fruit 7 3 2 4

I need to on another page do the following:
If D1=car and D2=box AND A4=apple and B4=pear and C4=fruit, bring back "5"
Can anyone help? Thanks

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(D4:G5,MATCH(1,(A4:A5="apple")*(B4:B5="pear" )*(C4:C5="fruit"),0),MATCH(1,(D1:G1="car")*(D2:G2= "box"),0))

OR, use cell references to hold the criteria:

J1 = apple
J2 = pear
J3 = fruit

K1 = car
K2 = box

=INDEX(D4:G5,MATCH(1,(A4:A5=J1)*(B4:B5=J2)*(C4:C5= J3),0),MATCH(1,(D1:G1=K1)*(D2:G2=K2),0))

Biff

"Deeds" wrote in message
...
Here it is:
car bus eat walk
box cat rug talk
apple pear fruit 5 10 15 20
apple corn fruit 7 3 2 4

I need to on another page do the following:
If D1=car and D2=box AND A4=apple and B4=pear and C4=fruit, bring back
"5"
Can anyone help? Thanks

Thanks....however I did exactly as you have it and did the ctrl shift enter
and I get the message box: Array formulas are not valid in merged cells.
Can you help with this?

"Biff" wrote:

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(D4:G5,MATCH(1,(A4:A5="apple")*(B4:B5="pear" )*(C4:C5="fruit"),0),MATCH(1,(D1:G1="car")*(D2:G2= "box"),0))

OR, use cell references to hold the criteria:

J1 = apple
J2 = pear
J3 = fruit

K1 = car
K2 = box

=INDEX(D4:G5,MATCH(1,(A4:A5=J1)*(B4:B5=J2)*(C4:C5= J3),0),MATCH(1,(D1:G1=K1)*(D2:G2=K2),0))

Biff

"Deeds" wrote in message
...
Here it is:
car bus eat walk
box cat rug talk
apple pear fruit 5 10 15 20
apple corn fruit 7 3 2 4

I need to on another page do the following:
If D1=car and D2=box AND A4=apple and B4=pear and C4=fruit, bring back
"5"
Can anyone help? Thanks

Hi!

Merged cells are a cancer in spreadsheets!

Unmerge the cells!

You can get the same "effect" without the problems:

FormatCellsAlignmentHorizontalCenter across selection.

What cells are merged?

Biff

"Deeds" wrote in message
...
Thanks....however I did exactly as you have it and did the ctrl shift
enter
and I get the message box: Array formulas are not valid in merged cells.
Can you help with this?

"Biff" wrote:

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(D4:G5,MATCH(1,(A4:A5="apple")*(B4:B5="pear" )*(C4:C5="fruit"),0),MATCH(1,(D1:G1="car")*(D2:G2= "box"),0))

OR, use cell references to hold the criteria:

J1 = apple
J2 = pear
J3 = fruit

K1 = car
K2 = box

=INDEX(D4:G5,MATCH(1,(A4:A5=J1)*(B4:B5=J2)*(C4:C5= J3),0),MATCH(1,(D1:G1=K1)*(D2:G2=K2),0))

Biff

"Deeds" wrote in message
...
Here it is:
car bus eat walk
box cat rug talk
apple pear fruit 5 10 15 20
apple corn fruit 7 3 2 4

I need to on another page do the following:
If D1=car and D2=box AND A4=apple and B4=pear and C4=fruit, bring back
"5"
Can anyone help? Thanks

I removed any merged cells....it works great....thanks a bunch!

"Biff" wrote:

Hi!

Merged cells are a cancer in spreadsheets!

Unmerge the cells!

You can get the same "effect" without the problems:

FormatCellsAlignmentHorizontalCenter across selection.

What cells are merged?

Biff

"Deeds" wrote in message
...
Thanks....however I did exactly as you have it and did the ctrl shift
enter
and I get the message box: Array formulas are not valid in merged cells.
Can you help with this?

"Biff" wrote:

Hi!

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(D4:G5,MATCH(1,(A4:A5="apple")*(B4:B5="pear" )*(C4:C5="fruit"),0),MATCH(1,(D1:G1="car")*(D2:G2= "box"),0))

OR, use cell references to hold the criteria:

J1 = apple
J2 = pear
J3 = fruit

K1 = car
K2 = box

=INDEX(D4:G5,MATCH(1,(A4:A5=J1)*(B4:B5=J2)*(C4:C5= J3),0),MATCH(1,(D1:G1=K1)*(D2:G2=K2),0))

Biff

"Deeds" wrote in message
...
Here it is:
car bus eat walk
box cat rug talk
apple pear fruit 5 10 15 20
apple corn fruit 7 3 2 4

I need to on another page do the following:
If D1=car and D2=box AND A4=apple and B4=pear and C4=fruit, bring back
"5"
Can anyone help? Thanks