find and get text using other list

tOpasio

New Member
Joined
Sep 26, 2014
Messages
15
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
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

mcmahobt

Board Regular
Joined
Sep 2, 2014
Messages
55
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

New Member
Joined
Sep 26, 2014
Messages
15
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

Board Regular
Joined
Sep 2, 2014
Messages
55
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

New Member
Joined
Sep 26, 2014
Messages
15

ADVERTISEMENT

yes it is
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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

New Member
Joined
Sep 26, 2014
Messages
15

ADVERTISEMENT

thanks for helping.. really appreciate
 

XOR LX

Well-known Member
Joined
Jul 2, 2012
Messages
4,517
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
 

Watch MrExcel Video

Forum statistics

Threads
1,109,325
Messages
5,528,023
Members
409,799
Latest member
mlewan_ca

This Week's Hot Topics

Top