VLOOKUP - dealing with lookup_value differences
 

I have the following basic VLOOKUP function that works great in most cases.

=IF(ISNA(VLOOKUP(P12,Sheet2!$A$1:$B$20,2,0)),"",VL OOKUP(P12,Sheet2!$A$1:$B$20,2,0))
(Note: I used the fill feature to fill downward through a few thousand rows.)

In some cases, there might be multiple lookup values within a cell, and I
have a situation such as the following:

Cell P12 might have the lookup value of "CD".
Cell P13 might have the lookup value of "HJ".
Cell P14 might have the lookup values of "CD HJ".

The original VLOOKUP function works great for P12 and P13, but obviously
runs into trouble with P14. Is there a way to concatenate the lookup values
so that P14 returns both (or several) lookup values separating each result
with a comma followed by a space? Or in Sheet 2, do I need to create a unique
entry manually defining what CD HJ is, just like CD and HJ are defined
independently?

Hi!

Since this formula is copied down a range and is specific using each cell in
the range as the lookup value:

do I need to create a unique entry manually defining what CD HJ is, just
like CD and HJ are defined independently?

YES !

If you had just a single lookup formula and a user inputted lookup value,
then you could use a formula with wildcards.

Biff

"Cheese" wrote in message
...
I have the following basic VLOOKUP function that works great in most cases.

=IF(ISNA(VLOOKUP(P12,Sheet2!$A$1:$B$20,2,0)),"",VL OOKUP(P12,Sheet2!$A$1:$B$20,2,0))
(Note: I used the fill feature to fill downward through a few thousand
rows.)

In some cases, there might be multiple lookup values within a cell, and I
have a situation such as the following:

Cell P12 might have the lookup value of "CD".
Cell P13 might have the lookup value of "HJ".
Cell P14 might have the lookup values of "CD HJ".

The original VLOOKUP function works great for P12 and P13, but obviously
runs into trouble with P14. Is there a way to concatenate the lookup
values
so that P14 returns both (or several) lookup values separating each result
with a comma followed by a space? Or in Sheet 2, do I need to create a
unique
entry manually defining what CD HJ is, just like CD and HJ are defined
independently?