Vlookup multiple terms and return one value

[Guest]

I'm having some difficulties getting this done. I would like to lookup values
in first two columns (category and sub category) in a range in a separate
table with 3 columns (match first two columns in first sheet to first two
columns in second table, and if both matches, return value in column 3 in the
second table).
First sheet will contain data over 2000 rows, while the lookup table
contains a fixed 3 columns by 150 rows.

example:

Prod Sub Hours D | 1 2
3
|
A a1 120 | A
a1 2
A a2 180 | A
a2 1
B b1 140 | B
b1 4
B b1 160 | B
b1 4
C c1 200 | C
c1 3

Any help is greatly appreciated.
Thanks.

 

[Bob Phillips]

=INDEX(Sheet2!C2:C20,MATCH(1,(Sheet2!A2:A20="A")*(Sheet2!B2:B20="a1"),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thanks Bob. It worked, but how do I copy the formula down? do I need to
change from relative to absolute, or is there a tricky way of doing this?

Thanks.

 

[Bob Phillips]

Make the fixed parts absolute, the dynamic parts relative.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Nevermind. I figured it out. Thanks a bunch.

 

[njuneardave]

I have a similar problem but I have never used excel functions before
and could use major help:


i have 3 sheets: SheetA, SheetB, SheetC.

Sheets A and B are the same originally, but after I run a program,
results get put into SheetA, altering the data. I want to compare the
results in SheetA with SheetB to see the differences.

Here is a mockup of the table:

SheetB:
Name size weight life type
--------------------------------------
cat 12 23 13 estab
cat 15 28 11 nat
cat 8 14 8 mut
cat 18 31 10 hybrid
dog 15 40 9 herd
dog 10 21 12 hound
dog 21 55 9 nonsp
dog 25 63 13 sport
dog 12 15 15 terrier
dog 10 9 14 toy
dog 13 12 12 toy1
dog 14 15 11 toy2



SheetA:
Name size weight life type
--------------------------------------
cat 12 23 13 estab
cat 15 28 11 nat
cat 8 14 8 mut
cat 18 31 10 hybrid
cat 90 90 100 WORK
dog 15 40 9 herd
dog 10 21 12 hound
dog 21 55 9 nonsp
dog 25 63 13 sport
dog 12 15 15 terrier
dog 10 9 14 toy
dog 13 12 12 WORK
dog 14 15 11 toy1

If I do VLOOKUP for WORK on SheetA....i will get the WORK pertaining to
the CAT not the DOG.....i need the one for the dog. how do i do this?
could you explain ur answer earlier in fuller detail so a newbie like
me can understand?


Thanks ahead of time....