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
Highlighted

@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))
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
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies