Vlookup 2 conditional format

[oeyandyprawira]

Hi, Please kindly help

I Have data in C1:G4

I need column B2:B12 will match the Item from column A & column C as long as column D housekeeping will take value from column G

I need column B15:B25 will match the Item from column A & column C as long as column D non fb banquet will take value from column G

Regards,
Andy

[samba_ravi]

pls attach sample excel file with some more examples and desired result

[oeyandyprawira]

pls attach sample excel file with some more examples and desired result

Ok, here it is , sheet before is before the formula vlookup,
sheet after is what to be after using vlookup.thx

[samba_ravi]

B2=IFERROR(INDEX($G:$G,MIN(INDEX((($C$1:$C$4<>$A2)+($D$1:$D$4<>"Housekeeping"))*10^10+ROW($D$1:$D$4),0))),"")

Please Login or Register  to view this content.

B15=IFERROR(INDEX($G:$G,MIN(INDEX((($C$1:$C$4<>$A15)+($D$1:$D$4<>"Non FB Banquet"))*10^10+ROW($D$1:$D$4),0))),"")

Please Login or Register  to view this content.

OR
b2=SUMIFS($G$1:$G$4,$C$1:$C$4,$A2,$D$1:$D$4,"Housekeeping")

Please Login or Register  to view this content.

B15=SUMIFS($G$1:$G$4,$C$1:$C$4,$A15,$D$1:$D$4,"Non FB Banquet")

Please Login or Register  to view this content.

[oeyandyprawira]

B2=IFERROR(INDEX($G:$G,MIN(INDEX((($C$1:$C$4<>$A2)+($D$1:$D$4<>"Housekeeping"))*10^10+ROW($D$1:$D$4),0))),"")

Please Login or Register  to view this content.

B15=IFERROR(INDEX($G:$G,MIN(INDEX((($C$1:$C$4<>$A15)+($D$1:$D$4<>"Non FB Banquet"))*10^10+ROW($D$1:$D$4),0))),"")

Please Login or Register  to view this content.

OR
b2=SUMIFS($G$1:$G$4,$C$1:$C$4,$A2,$D$1:$D$4,"Housekeeping")

Please Login or Register  to view this content.

B15=SUMIFS($G$1:$G$4,$C$1:$C$4,$A15,$D$1:$D$4,"Non FB Banquet")

Please Login or Register  to view this content.

Perfect, thx.

[samba_ravi]

You are welcome and thanks for your feedback

[oeyandyprawira]

You are welcome and thanks for your feedback

Is it possible to sum more than 1 reference, for example housekeeping , housekeeping store, housekeeping food & housekeeping beverage?

I attached the sample

[samba_ravi]

b2=SUMIFS($G$1:$G$7,$C$1:$C$7,$A2,$D$1:$D$7,"*HOUSEKEEPING*")

Please Login or Register  to view this content.

Try this and copy towards down

[oeyandyprawira]

b2=SUMIFS($G$1:$G$7,$C$1:$C$7,$A2,$D$1:$D$7,"*HOUSEKEEPING*")

Please Login or Register  to view this content.

Try this and copy towards down

Thx it works, what if reference no 2 is front office , reference no 3 is fb kitchen. Is it possible?