find and get text using other list

[tOpasio]

hi guys..


a1:race12345-2win
b1:flamegx55hex
c1:theacefc312mobs

sheet2
a1:12345-2
b1:gx55
c1:fc312
d1:xx314
e1:hs556

question
how can i get the specific text or value using my list in sheet2

 

[mcmahobt]

If you mean how dan you reference your data on Sheet 2 to create the values in A1, B1, and C1 on Sheet 1, then you can use CONCATENATE or a combination of & to combine text.

Otherwise, you need to elaborate on what you're trying to do.

 

[tOpasio]

no.. i dont need to combine text

i need to get the exact text..

1st i need to know if in that celll(a1) is available in my list (sheet2)
if so, i should get the match value (12345-2.)

 

[mcmahobt]

Do you want the text strings in Sheet 2 to cycle through all the data sets in Sheet 1 to see if they reside in them?

 

[tOpasio]

yes it is

 

[XOR LX]

Hi.

In Sheet1 B1:

=LOOKUP(2^15,SEARCH(Sheet2!$A$1:$A$5,Sheet1!A1),Sheet2!$A$1:$A$5)

Copy down as required.

Regards

 

[tOpasio]

thanks for helping.. really appreciate

 

[tOpasio]

just for my understanding.. what is the purpose of 2*15?

 

[XOR LX]

You're welcome.

The largest possible numerical return from the SEARCH function is 32,767. Since 2^15=32,768, we are guaranteed that our choice of lookup_value is sufficiently large here.

Regards