Search string within substring range

[tiredofit]

I have the following column of data:

Description

ten bananas

rotten smelly apples

very ripe oranges

sour lemons to be binned

blue limes?

I need to look up each row from the above table in the following table to find a partial match:

Fruits

apple fair

really sweet pineapple

lemon or not

lime from abroad

The answer would be:

Sentence	Match?
ten bananas	no match
rotten smelly apples	apple fair
very ripe oranges	no match
sour lemons to be binned	lemon or not
blue limes?	lime from abroad

Is this possible WITHOUT VBA?

The best I have come up with, so far is this formula:

=SUMPRODUCT(COUNTIF(A2,"*"&substrings&"*"))>0

taken from:

Range contains one of many substrings

To test a range and determine if it contains one of many substrings (partial matches, specific text, etc.) you can use use a formula based on the COUNTIF function nested in the SUMPRODUCT function.

exceljet.net

where substrings is the second table range from apple fair to lime from abroad.

but this only returns a TRUE or FALSE. It doesn't return the value, eg apple fair or lime from abroad.

Thanks

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, why isn't "lemon or not" the result beside "very ripe oranges"?

And what would be the expected result if the description was "lime nor pineapple" since that contains "apple" from the 1st fruit list item, "pineapple" from the 2nd, "or" from the 3rd and "lime" from the 4th?

 

[tiredofit]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Also, why isn't "lemon or not" the result beside "very ripe oranges"?

And what would be the expected result if the description was "lime nor pineapple" since that contains "apple" from the 1st fruit list item, "pineapple" from the 2nd, "or" from the 3rd and "lime" from the 4th?

Thanks for your reply.

With regard to why "lemon or not" is not beside "very ripe oranges", upon reflection, I think it should be, though this scenario was was taken into account when explained to me initially.

Similarly with "lime nor pineapple", I think the result should be "apple fair really sweet pineapple lemon or not lime from abroad".

 

[Peter_SSs]

Is this possible WITHOUT VBA?

I would definitely be using vba, but since you asked if possible with formulas (& you have Excel 2019), if you are happy with a blank instead of "no match" ..

tiredofit.xlsm
	A	B
1	Description	Match?
2	ten bananas
3	rotten smelly apples	apple fair
4	very ripe oranges	lemon or not
5	sour lemons to be binned	lemon or not
6	blue limes?	lime from abroad
7	lime nor pineapple	apple fair really sweet pineapple lemon or not lime from abroad
8
9	Fruits
10	apple fair
11	really sweet pineapple
12	lemon or not
13	lime from abroad
14
Lookup

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTJOIN(" ",1,IF(ISNUMBER(SEARCH(LEFT(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,TRIM(CONCAT(SUBSTITUTE(A$10:A$13&" "," ","|"&ROW(A$10:A$13)-ROW(A$10)+1&" "))))," ","</c><c>")&"</c></p>","//c"),FIND("|",FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,TRIM(CONCAT(SUBSTITUTE(A$10:A$13&" "," ","|"&ROW(A$10:A$13)-ROW(A$10)+1&" "))))," ","</c><c>")&"</c></p>","//c"))-1),A2)),INDEX(A$10:A$13,REPLACE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,TRIM(CONCAT(SUBSTITUTE(A$10:A$13&" "," ","|"&ROW(A$10:A$13)-ROW(A$10)+1&" "))))," ","</c><c>")&"</c></p>","//c"),1,FIND("|",FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,TRIM(CONCAT(SUBSTITUTE(A$10:A$13&" "," ","|"&ROW(A$10:A$13)-ROW(A$10)+1&" "))))," ","</c><c>")&"</c></p>","//c")),"")),""))

The "no match" could also be achieved, but the formula would be even longer!!

Both CONCAT & TEXTJOIN have limits on the length of string they can create so if your Fruit list is very long there could be problems anyway.

Also note that if the Description includes 2 or more word matches from the same item in Fruit list then the Fruit list item will appear in the result multiple times. For example if the Description was
"Go abroad & bring limes from India" which has three words matching from cell A13 then the result will include A13 repeated 3 times:
"lime from abroad lime from abroad lime from abroad"

 

[tiredofit]

I would definitely be using vba, but since you asked if possible with formulas (& you have Excel 2019), if you are happy with a blank instead of "no match" ..

tiredofit.xlsm
	A	B
1	Description	Match?
2	ten bananas
3	rotten smelly apples	apple fair
4	very ripe oranges	lemon or not
5	sour lemons to be binned	lemon or not
6	blue limes?	lime from abroad
7	lime nor pineapple	apple fair really sweet pineapple lemon or not lime from abroad
8
9	Fruits
10	apple fair
11	really sweet pineapple
12	lemon or not
13	lime from abroad
14
Lookup

Cell Formulas
Range	Formula
B2:B7	B2	=TEXTJOIN(" ",1,IF(ISNUMBER(SEARCH(LEFT(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,TRIM(CONCAT(SUBSTITUTE(A$10:A$13&" "," ","|"&ROW(A$10:A$13)-ROW(A$10)+1&" "))))," ","</c><c>")&"</c></p>","//c"),FIND("|",FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,TRIM(CONCAT(SUBSTITUTE(A$10:A$13&" "," ","|"&ROW(A$10:A$13)-ROW(A$10)+1&" "))))," ","</c><c>")&"</c></p>","//c"))-1),A2)),INDEX(A$10:A$13,REPLACE(FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,TRIM(CONCAT(SUBSTITUTE(A$10:A$13&" "," ","|"&ROW(A$10:A$13)-ROW(A$10)+1&" "))))," ","</c><c>")&"</c></p>","//c"),1,FIND("|",FILTERXML("<p><c>"&SUBSTITUTE(TEXTJOIN("</c><c>",1,TRIM(CONCAT(SUBSTITUTE(A$10:A$13&" "," ","|"&ROW(A$10:A$13)-ROW(A$10)+1&" "))))," ","</c><c>")&"</c></p>","//c")),"")),""))

The "no match" could also be achieved, but the formula would be even longer!!

Both CONCAT & TEXTJOIN have limits on the length of string they can create so if your Fruit list is very long there could be problems anyway.

Also note that if the Description includes 2 or more word matches from the same item in Fruit list then the Fruit list item will appear in the result multiple times. For example if the Description was
"Go abroad & bring limes from India" which has three words matching from cell A13 then the result will include A13 repeated 3 times:
"lime from abroad lime from abroad lime from abroad"

Many thanks, yes blank is fine.

I too agree VBA is preferable but my client is reluctant to learn it (for future maintenance) and is under the impression it would be easier to amend formulae.

Perhaps after seeing this solution, he MIGHT change his mind,

BTW, what are these?

</c> and </p>

Are they html tags or part of your excel formula?

 

[Peter_SSs]

Many thanks, yes blank is fine.

You're welcome.

Perhaps after seeing this solution, he MIGHT change his mind,

You would think/hope so!

BTW, what are these?

</c> and </p>

Are they html tags or part of your excel formula?

They are part of the formula - used by the FILTERXML function.

BTW, what Excel version and operating system is your client using? This suggestion was on the assumption of Excel 2019/Windows per your profile.

 

[tiredofit]

You're welcome.


You would think/hope so!


They are part of the formula - used by the FILTERXML function.

BTW, what Excel version and operating system is your client using?

Thanks for explaining.

I'm afraid I wasn't told my client's Excel / OS. I suspect all I can do is send him the file with your solution and see if it works or not.

My impression is Excel has advanced quite a bit over the last decade, especially with the new array functions but thankfully VBA hasn't changed much (if at all) and so VBA solutions should be safer.

 

[Peter_SSs]

I'm afraid I wasn't told my client's Excel / OS.

Ok, fair enough.
I was asking because TEXTJOIN & CONCAT are only available in Excel 2019 or Microsoft 365 and FILTERXML is not available on Mac operating systems for any version of Excel.
However, if the client had Microsoft 365 on Windows, then the formula could be shortened considerably & the possible duplications mentioned could also be eliminated.

 

[tiredofit]

Ok, fair enough.
I was asking because TEXTJOIN & CONCAT are only available in Excel 2019 or Microsoft 365 and FILTERXML is not available on Mac operating systems for any version of Excel.
However, if the client had Microsoft 365 on Windows, then the formula could be shortened considerably & the possible duplications mentioned could also be eliminated.

Thanks for clarifying, I'll ask and update when / if I receive a response.

One problem I've noticed with O365 is not all functions are available to all subscribers. They're rolled out in some order, so it's possible for some to have access to functions that others don't, eg FILTER.

 

[Peter_SSs]

They're rolled out in some order, so it's possible for some to have access to functions that others don't, eg FILTER.

That is true, but I think all subscribers should theoretically have access to all functions except the LET() function by now. I say theoretically because some people do not have some functions only because they or their IT department have not installed all available updates.