# find and get text using other list

#### tOpasio

##### New Member
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

### 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
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
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
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?

ADVERTISEMENT

yes it is

#### XOR LX

##### Well-known Member
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

ADVERTISEMENT

thanks for helping.. really appreciate

#### tOpasio

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

#### XOR LX

##### Well-known Member
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

Replies
23
Views
979
Replies
1
Views
30
Replies
5
Views
71
Replies
5
Views
82
Replies
1
Views
35

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