Vlookup multiple terms and return one value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sahafi - 21 Jun 2006 06:33 GMT

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.

Signature

when u change the way u look @ things, the things u look at change.

Reply to this Message

Bob Phillips - 21 Jun 2006 09:11 GMT

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

Signature

HTH

Bob Phillips

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

> 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
[quoted text clipped - 22 lines]
> Any help is greatly appreciated.
> Thanks.

Reply to this Message

sahafi - 21 Jun 2006 18:07 GMT

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.

Signature

when u change the way u look @ things, the things u look at change.

> =INDEX(Sheet2!C2:C20,MATCH(1,(Sheet2!A2:A20="A")*(Sheet2!B2:B20="a1"),0))
>
[quoted text clipped - 29 lines]
> > Any help is greatly appreciated.
> > Thanks.

Reply to this Message

Bob Phillips - 21 Jun 2006 18:10 GMT

Make the fixed parts absolute, the dynamic parts relative.

Signature

HTH

Bob Phillips

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

> 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?
[quoted text clipped - 34 lines]
> > > Any help is greatly appreciated.
> > > Thanks.

Reply to this Message

sahafi - 21 Jun 2006 20:22 GMT

Nevermind. I figured it out. Thanks a bunch.

Signature

when u change the way u look @ things, the things u look at change.

Reply to this Message

njuneardave - 21 Jun 2006 21:11 GMT

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

> Nevermind. I figured it out. Thanks a bunch.
>
[quoted text clipped - 30 lines]
> > --
> > when u change the way u look @ things, the things u look at change.

Reply to this Message