SOLVED

Search find in excel

Copper Contributor

This may be a basic question, but I have been looking for the way to find an answer for this over the past few days. 

 

Suppose sheet 1, ColumnA:

123

456

789

 

Then I have another sheet2, columnA

1724141

3415123

5123456

3123451

5123789

 

Is there a way for me to populate in columnB from sheet2 if the substring is found? my expectation is 

columnA    columnB

123             3415123 (columnB contains '123')

456             5123456 (columnB contains '456')

789             5123789 (columnB contains '789')

 

Thanks

8 Replies

@hnandarusdy , substring 123 in all of

3415123

5123456

3123451

5123789

You'd like to compare only last characters, or return first found or what?

You may try this formula in Sheet2!B1, copied down rows:
=INDEX(A:A,
MATCH(“*”&Sheet1!A1&”*”,
A:A,0)
The foregoing formula returns the first value in Column A that contains the value in Sheet1!A1.

@Twifoo , the only point the formula shall return result into column B of Sheet1 from A:A in Sheet2.

Perhaps, I misunderstood the requirement. The formula should be in Sheet1!B1, copied down rows, which is:
=INDEX(Sheet2!A:A,
MATCH(“*”&A1&”*”,
Sheet2!A:A,0))
best response confirmed by hnandarusdy (Copper Contributor)
Solution

@Twifoo , at least that's my understanding of the question. In this its' part.

 

 

Hi @Sergei Baklan , thanks for your response. Actually the first found of the last characters. 

@Twifoo 

 

Thanks. 

 

This is absolutely the answer. One thing though, the cell must be in "text" format.

 

edit: I have mistakenly pressed the button of "best response" to  @Sergei Baklan  and I don't know how to change it :(

To convert the result to text, wrap the formula with TEXT, like this:
=TEXT(INDEX(Sheet2!A:A,
MATCH(“*”&A1&”*”,
Sheet2!A:A,0)),"#")
1 best response

Accepted Solutions
best response confirmed by hnandarusdy (Copper Contributor)
Solution

@Twifoo , at least that's my understanding of the question. In this its' part.

View solution in original post