SOLVED
Home

Search find in excel

%3CLINGO-SUB%20id%3D%22lingo-sub-568945%22%20slang%3D%22en-US%22%3ESearch%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-568945%22%20slang%3D%22en-US%22%3E%3CP%3EThis%20may%20be%20a%20basic%20question%2C%20but%20I%20have%20been%20looking%20for%20the%20way%20to%20find%20an%20answer%20for%20this%20over%20the%20past%20few%20days.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESuppose%20sheet%201%2C%20ColumnA%3A%3C%2FP%3E%3CP%3E123%3C%2FP%3E%3CP%3E456%3C%2FP%3E%3CP%3E789%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20I%20have%20another%20sheet2%2C%20columnA%3C%2FP%3E%3CP%3E1724141%3C%2FP%3E%3CP%3E3415123%3C%2FP%3E%3CP%3E5123456%3C%2FP%3E%3CP%3E3123451%3C%2FP%3E%3CP%3E5123789%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20there%20a%20way%20for%20me%20to%20populate%20in%20columnB%20from%20sheet2%20if%20the%20substring%20is%20found%3F%20my%20expectation%20is%26nbsp%3B%3C%2FP%3E%3CP%3EcolumnA%26nbsp%3B%20%26nbsp%3B%20columnB%3C%2FP%3E%3CP%3E123%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B3415123%20(columnB%20contains%20'123')%3C%2FP%3E%3CP%3E456%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B5123456%26nbsp%3B(columnB%20contains%20'456')%3C%2FP%3E%3CP%3E789%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B5123789%26nbsp%3B(columnB%20contains%20'789')%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-568945%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBI%20%26amp%3B%20Data%20Analysis%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-569131%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-569131%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F342406%22%20target%3D%22_blank%22%3E%40hnandarusdy%3C%2FA%3E%26nbsp%3B%2C%20substring%20123%20in%20all%20of%3C%2FP%3E%0A%3CP%3E3415123%3C%2FP%3E%0A%3CP%3E5123456%3C%2FP%3E%0A%3CP%3E3123451%3C%2FP%3E%0A%3CP%3E5123789%3C%2FP%3E%0A%3CP%3EYou'd%20like%20to%20compare%20only%20last%20characters%2C%20or%20return%20first%20found%20or%20what%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-569363%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-569363%22%20slang%3D%22en-US%22%3EYou%20may%20try%20this%20formula%20in%20Sheet2!B1%2C%20copied%20down%20rows%3A%3CBR%20%2F%3E%3DINDEX(A%3AA%2C%3CBR%20%2F%3EMATCH(%E2%80%9C*%E2%80%9D%26amp%3BSheet1!A1%26amp%3B%E2%80%9D*%E2%80%9D%2C%3CBR%20%2F%3EA%3AA%2C0)%3CBR%20%2F%3EThe%20foregoing%20formula%20returns%20the%20first%20value%20in%20Column%20A%20that%20contains%20the%20value%20in%20Sheet1!A1.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-569377%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-569377%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20the%20only%20point%20the%20formula%20shall%20return%20result%20into%20column%20B%20of%20Sheet1%20from%20A%3AA%20in%20Sheet2.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-569492%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-569492%22%20slang%3D%22en-US%22%3EPerhaps%2C%20I%20misunderstood%20the%20requirement.%20The%20formula%20should%20be%20in%20Sheet1!B1%2C%20copied%20down%20rows%2C%20which%20is%3A%3CBR%20%2F%3E%3DINDEX(Sheet2!A%3AA%2C%3CBR%20%2F%3EMATCH(%E2%80%9C*%E2%80%9D%26amp%3BA1%26amp%3B%E2%80%9D*%E2%80%9D%2C%3CBR%20%2F%3ESheet2!A%3AA%2C0))%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-569509%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-569509%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%2C%20at%20least%20that's%20my%20understanding%20of%20the%20question.%20In%20this%20its'%20part.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572025%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572025%22%20slang%3D%22en-US%22%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%2C%20thanks%20for%20your%20response.%20Actually%20the%20first%20found%20of%20the%20last%20characters.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572056%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572056%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F280482%22%20target%3D%22_blank%22%3E%40Twifoo%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20absolutely%20the%20answer.%20One%20thing%20though%2C%20the%20cell%20must%20be%20in%20%22text%22%20format.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eedit%3A%20I%20have%20mistakenly%20pressed%20the%20button%20of%20%22best%20response%22%20to%26nbsp%3B%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%26nbsp%3Band%20I%20don't%20know%20how%20to%20change%20it%20%3A(%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-572343%22%20slang%3D%22en-US%22%3ERe%3A%20Search%20find%20in%20excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-572343%22%20slang%3D%22en-US%22%3ETo%20convert%20the%20result%20to%20text%2C%20wrap%20the%20formula%20with%20TEXT%2C%20like%20this%3A%3CBR%20%2F%3E%3DTEXT(INDEX(Sheet2!A%3AA%2C%3CBR%20%2F%3EMATCH(%E2%80%9C*%E2%80%9D%26amp%3BA1%26amp%3B%E2%80%9D*%E2%80%9D%2C%3CBR%20%2F%3ESheet2!A%3AA%2C0))%2C%22%23%22)%3C%2FLINGO-BODY%3E
hnandarusdy
New 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?

Highlighted
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))
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)),"#")
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
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies