Vlookup cells in combination of two columns (both in the lookup value AND table array)

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Corrie - 07 Jan 2007 15:54 GMT

I am trying to match information in multiple columns (lookup value) to
the same multiple columns in the table array. If the combinations
don't match then I'd like the return to be #N/A. I think an example
can better clarify what I'm trying to accomplish because I haven't a
clue whether I can do it...

On the lookup worksheet I have...
A1 B1
12345 Closeout
12345 First Fill
22233 First Fill
22233 Closeout

On table array worksheet I only have one entry.
A1 B1
12345 Closeout
22233 First Fill

I would like the return to be...
A1 B1 C1
12345 Closeout Y
12345 First Fill #N/A
22233 First Fill Y
22233 Closeout #N/A

I would like the lookup value to combine A1 and B1 and look for that
exact combination on the table array worksheet.

This may be very simple but I am stumped.

Any help is greatly appreciated

Reply to this Message

CLR - 07 Jan 2007 16:29 GMT

Insert a new helper column before column A of your table array so your data
is in columns B and C....then in A1 put this and copy down........=B1&C1

then for your lookup formula on the lookup worksheet, use this in C1 and
copy down

=VLOOKUP(a1&b1,'TABLE ARRAY'!A:C,3,FALSE)

Vaya con Dios,
Chuck, CABGx3

> I am trying to match information in multiple columns (lookup value) to
> the same multiple columns in the table array. If the combinations
[quoted text clipped - 27 lines]
>
> Any help is greatly appreciated

Reply to this Message

Corrie - 07 Jan 2007 16:51 GMT

Wow. That seemed to work. Looking at what you suggested, I don't
understand the logic of it. Would you mind explaining why having
another column with B1&C1 would help find what you are looking for in
two different columns?

Thanks!

> Insert a new helper column before column A of your table array so your data
> is in columns B and C....then in A1 put this and copy down........=B1&C1
[quoted text clipped - 38 lines]
> >
> > Any help is greatly appreciated

Reply to this Message

CLR - 07 Jan 2007 17:44 GMT

You were interested only in rows that had a specific value in column A,
matching with another specific value in column B....well, by CONCATENATING
the two columns together in the lookup array, the VLOOKUP formula only has
to search one column (which is all it can do) to find a row with the
combination you're looking for..........

hth
Vaya con Dios,
Chuck, CABGx3

> Wow. That seemed to work. Looking at what you suggested, I don't
> understand the logic of it. Would you mind explaining why having
[quoted text clipped - 45 lines]
> > >
> > > Any help is greatly appreciated

Reply to this Message