Lookup value on another sheet that contains multiple values in a single cell (separated by commas) and then return the values in the next column

[HAIER]

Sorry if that's a bit long winded...

Here's how it breaks down.

If I have, in one cell (call it D1):

MSKU8475060

and in a lookup table on another sheet (may contain spaces between value and commas):
A
1MAEU862993750 , EGLV010101314024, COSU6074003190, MSKU8475060 , etc....
2 etc....
I need to be able to lookup the value in D1 on the table and return the values in column B to a single cell (say E1)


Does that make sense or should I resubmit?

I've tried a few different ways but can't come up with anything...

There's more to the project, but this bit will help me get it off the ground and hopefully I'll be able to string the rest together.

Cheers guys! Great forum!

 

[Aladin Akyurek]

Sorry if that's a bit long winded...

Here's how it breaks down.

If I have, in one cell (call it D1):

MSKU8475060

and in a lookup table on another sheet (may contain spaces between value and commas):
A
1MAEU862993750 , EGLV010101314024, COSU6074003190, MSKU8475060 , etc....
2 etc....
I need to be able to lookup the value in D1 on the table and return the values in column B to a single cell (say E1)


Does that make sense or should I resubmit?

I've tried a few different ways but can't come up with anything...

There's more to the project, but this bit will help me get it off the ground and hopefully I'll be able to string the rest together.

Cheers guys! Great forum!

Something like...

=LOOKUP(9.99999999999999E+307,
     SEARCH(","&D1&",",","&SUBSTITUTE(Sheet2!$A$2:$A$20," ","")&","),
     Sheet2!$B$2:$B$20)

 

[HAIER]

Aladin, Thank you so much! Your code solved my question. I know it's a silly question but what if the desired result would be to return the values in column A instead of returning the values in column b. I am hoping this will help me understand the code better. BTW: Preferably different from the following:=LOOKUP(9.99999999999999E+307,
SEARCH(","&D1&",",","&SUBSTITUTE(Sheet2!$A$2:$A$20," ","")&","),
Sheet2!$A$2:$A$20)

 

[Aladin Akyurek]

Aladin, Thank you so much! Your code solved my question.

You are welcome.

I know it's a silly question but what if the desired result would be to return the values in column A instead of returning the values in column b. I am hoping this will help me understand the code better. BTW: Preferably different from the following:=LOOKUP(9.99999999999999E+307,
SEARCH(","&D1&",",","&SUBSTITUTE(Sheet2!$A$2:$A$20," ","")&","),
Sheet2!$A$2:$A$20)

Whether we return a result from the search range (range in column A) or from the range in column B does not matter. in waht follows, I'll try to explain how the formula works.

1) The formula (abstracted)

LOOKUP(LookUpValue,SearchReference,ResultReference)

finds the LookUpValue in a cell in the search reference and returns a result from the corresponding/correlating cell from the result reference. In our case LookUpValue is 9.99999999999999E+307 (a constant from Excel itself, listed under "limits" in help), the search reference is a SEARCH expression involving a range in column A, and the result reference is the same range, used in the search reference.

2) LOOKUP ignores error values if it can and, when a big number is used as in our case, it returns the last cell with a numeric value from the search reference.

3) The search reference in our case consists of a SEARCH expression:

SEARCH(SearchValue,SearchRange)

4) SearchValue is the cell D1 whose value is concatenated with a comma in front and in back:

"," & D1 & ","

MSKU8475060 becomes thus: ,MSKU8475060,

5) SearchReference is the range A2:A20 (on Sheet1, omitted below for ease) of which each cell becomes spaceless with:

SUBSTITUTE(A2:A20, " ", "")

and also concatenated with a comma in front and in back:

"," & SUBSTITUTE(A2:A20, " ", "") & ","

Thus the long string in A2 loses its spaces and gets commas around. This happens with each cell...

MAEU862993750 , EGLV010101314024, COSU6074003190, MSKU8475060 ,

is in a A-cell gets transformed into:

,MAEU862993750,EGLV010101314024,COSU6074003190,MSKU8475060,

Note that SEARCH will invariably find the substring of (4) in the above string.

6) LOOKUP will correlate the cell found in (5) with the cell in the result reference.

A possible re-write is:

Control+shift+enter, not just enter:

=INDEX(Sheet1!$A$2:$A$20,MATCH("*,"&D1&",*",","&SUBSTITUTE(Sheet1!$A$2:$A$20," ","")&",",0))

Another, just enter:

=VLOOKUP("*"&D1&"*",Sheet1!$A$2:$A$20,1,0)

The latter can be risky for it can fire up on strings having a substring similar (but not equal) to the one in D1.

Hope this helps.

 

[HAIER]

Aladin,

Most appreciated. You have been more than generous with your replies. Thank you so much for your time.

 

[Aladin Akyurek]

Aladin,

Most appreciated. You have been more than generous with your replies. Thank you so much for your time.

You are welcome. Thanks for providing feedback.