CODEWITHSUNDEEP
excelexcel-formulalookup
Stanleyrr
Stanleyrr

Reputation: 875

Paste leading common substrings from another sheet

In Excel, Sheet1 I have the following column in one sheet that contains names:

enter image description here

In Sheet2 I have a column called substring as shown in the screenshot below (for simplicity I just use 1 row):

enter image description here

For each name in Sheet1, I want to check if it contains any of the substrings in Sheet2, which is "abreu" in this case. If the name contains the substring from Sheet2 AND the substring is in the beginning of the name, then paste the substring to the cell next to it. If the name doesn't contain the substring, then don't do anything. The desired output should look like the screenshot below:

enter image description here

I have tried VLOOKUP which doesn't quite work because it's either full match or partial match and none of which fit my case here. I also tried the SEARCH function which also doesn't detect whether the substring is in the beginning of the name. Any help is greatly appreciated.

Upvotes: 1

Views: 43

Answers (1)

BigBen
BigBen

Reputation: 50008

Something like this should work. I have both lists on the same sheet, but you can easily modify the formula to reflect the 2-sheet setup.

=INDEX($D$2:$D$4,AGGREGATE(15,6,ROW($A$1:$A$3)/ISNUMBER(SEARCH($D$2:$D$4,LEFT(A2,LEN($D$2:$D$4)))),1))

enter image description here

You can also add an IFERROR to return "" if no match.

Upvotes: 2

Related Questions