• 412K Members
• 10.2K Online
• 470K Conversations
SOLVED

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

Highlighted

# 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
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Edge insider Dev bypasses IE mode website list
HotCakeX in Enterprise on
4 Replies