|
Vlookup "crosstab"
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 |
| All times are GMT +1. The time now is 01:19 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com