Vlookup with two reference columns

P

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
 
D

Domenic

Hi,

One way...

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

entered using CONTROL+SHIFT+ENTER

Hope this helps!
 
G

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.
 
F

Frank Kabel

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top