Vlookup "crosstab"

[Guest]

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

 

[Domenic]

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!

 

[Biff]

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

 

[Guest]

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]

Hi!

Merged cells are a cancer in spreadsheets!

Unmerge the cells!

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

Format>Cells>Alignment>Horizontal>Center across selection.

What cells are merged?

Biff

 

[Guest]

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