Vlookup with two reference columns

[Prashanth]

I need to pick up a value from a table that satisfies conditions on
columns "A" as well as column "B"

The sample of the data i have is given below

Region Type Value
America Y 100
America N 200
Asia Y 150
Asia N 250
Australia Y 300
Australia N 100
Europe Y 200
Europe N 150

In Cell (C20) I need to write a vlookup formula which will pick up the
value satisfying both conditions for region "America" and type "Y" = 100

Any suggestions? Please help

Regards,
Prashanth

 

[Domenic]

Hi,

One way...

=INDEX(C2:C9,MATCH(1,(A2:A9="America")*(B2:B9="Y"),0))

entered using CONTROL+SHIFT+ENTER

Hope this helps!

 

[Govind]

Hi Prashanth,

Try =SUMPRODUCT((E7:E14="America")*(F7:F14="Y")*(G7:G14))

make sure there are no spaces after your region/type values.

Regards

Govind.

 

[Frank Kabel]

Hi
try the array formula (entered with CTRL+SHIFT+ENTER):
=INDEX(C1:C20,MATCH(1,(A1:A20="America")*(B1:B20="Y"),0))

 

[Govind]

Oops. Sorry . this one will sum all such values meeting the condition.

Govind.

 

[Prashanth]

Thank you all. It works

Very useful

Regards,
Prashanth