Complex lookup with multiple criteria

[uday]

Hi,

Well, I want to custom a lookup formula in lookup sheet where numbers (2,3,4 & 65,12,35) in column C will be reflected based on criteria in column B (Foods,Liquors,Fruits) and A (Regions, country) and look up value will be 650066,652023,233333.

Regards,
Uday

 

[GraH - Guido]

Perhaps use a reference index-match. See attached.
I have not made the area number dynamic, so it is not optimal.

 

[bosco_yip]

Or…………………..

In "Lookup" sheet C1, copied down :

=IF(B1="","",INDEX(('Data sheet'!C$1:C$3,'Data sheet'!C$7:C$9),MATCH(2,MMULT(--(A1:B1='Data sheet'!A$1:B$3),{1;1}),),,MATCH(LOOKUP("zz",A$1:A4),{"Regions";"Country"},0)))

Regards
Bosco