• 411K Members
• 5,966 Online
• 467K Conversations
SOLVED

Highlighted
New Contributor

Search find in excel

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

Re: Search find in excel

@hnandarusdy , substring 123 in all of

3415123

5123456

3123451

5123789

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

Re: Search find in excel

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.

Re: Search find in excel

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

Re: Search find in excel

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))
Solution

Re: Search find in excel

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

Re: Search find in excel

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

Re: Search find in excel

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 :(

Re: Search find in excel

To convert the result to text, wrap the formula with TEXT, like this:
=TEXT(INDEX(Sheet2!A:A,
MATCH(“*”&A1&”*”,
Sheet2!A:A,0)),"#")
Related Conversations
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies