Name Merger

[isabelly1122]

I need a formula to take two lists of words and if the end 2 or 3 letters or beginning 2 or 3 letters of one list match the end 2 or 3 letters or beginning 2 or 3 letters of the other list then to merge them and create a list with these merged words. I.e. list one: carrot, barge, office, tree and list two: rotten, equal, scoff, germinate then the list created will be: carrotten, scoffice, bargerminate

 

[p45cal]

Quick but ugly:

=IF(OR(LEFT(C6,2)=LEFT(D6,2),LEFT(C6,2)=RIGHT(D6,2),RIGHT(C6,2)=RIGHT(D6,2),RIGHT(C6,2)=LEFT(D6,2),LEFT(C6,3)=LEFT(D6,3),LEFT(C6,3)=RIGHT(D6,3),RIGHT(C6,3)=RIGHT(D6,3),RIGHT(C6,3)=LEFT(D6,3)),C6&D6,"n/a")

 

[p45cal]

I note that in your sample file you include a result Dell SpaceSpaceX, I'm guessing because the last 5 characters of Dell Space are the same as the first 5 characters of SpaceX, which doesn't tally with your:

if the end 2 or 3 letters or beginning 2 or 3 letters of one list match the end 2 or 3 letters or beginning 2 or 3 letters of the other list

If you want at least 2 characters to match but it can be any number then not so quick, and uglier:

=IF(OR(IFERROR(LEFT(C6,ROW(INDIRECT("A2:A" & LEN(C6))))=LEFT(D6,ROW(INDIRECT("A2:A" & LEN(D6)))),FALSE),IFERROR(LEFT(C6,ROW(INDIRECT("A2:A" & LEN(C6))))=RIGHT(D6,ROW(INDIRECT("A2:A" & LEN(D6)))),FALSE),IFERROR(RIGHT(C6,ROW(INDIRECT("A2:A" & LEN(C6))))=RIGHT(D6,ROW(INDIRECT("A2:A" & LEN(D6)))),FALSE),IFERROR(RIGHT(C6,ROW(INDIRECT("A2:A" & LEN(C6))))=LEFT(D6,ROW(INDIRECT("A2:A" & LEN(D6)))),FALSE)),C6&D6,"n/a")

You might need to commit this formula to the sheet with Ctrl + Shift + Enter rather than just Enter in your version of Excel which I think is Excel 2013. Try both ways.

If you've got a version of Excel which includes the Let() function then:

=LET(a,C6,b,D6,La,LEFT(a,ROW(INDIRECT("A2:A" & LEN(a)))),Lb,LEFT(b,ROW(INDIRECT("A2:A" & LEN(b)))),Ra,RIGHT(a,ROW(INDIRECT("A2:A" & LEN(a)))),Rb,RIGHT(b,ROW(INDIRECT("A2:A" & LEN(b)))),IF(OR(IFERROR(La=Lb,FALSE),IFERROR(La=Rb,FALSE),IFERROR(Ra=Rb,FALSE),IFERROR(Ra=Lb,FALSE)),a & b,"N/A"))

 

[isabelly1122]

Thanks p45cal, i guess 1st one quick but ugly is better for me to understand

 

[p45cal]

Thanks p45cal, i guess 1st one quick but ugly is better for me to understand

Yes, but it doesn't agree with your attachment's expected result Dell SpaceSpaceX

 

[isabelly1122]

yes i am having some trouble understanding
ROW(INDIRECT("A2:A" & LEN " Will defintely search on youtube for this application as this is giving the perfect result