Formula to identify a value within an array

[Saulos]

Hello everyone, I hope you are having a pleasant day!

I am not an expert Excel user, and I have been struggling to find a formula that can help me identify whether a value (in my case, text) is contained within an array.

Below the use-case:

Column D contains a sample set of "Product Names".

Column A contains a sample set of "Advertisement Titles" from eBay.

I want to know whether the product names from column D are contained in the titles from column A. The result to appear in column B, as shown below.

I tried using MATCH, SEARCH, XVLOOKUP and combinations thereof but none delivered the result I am looking for.

I am using Excel for Microsoft 365 Version 2202.

Thank you very much for your help.

 

[RobP]

Might I ask, what is the actual result you are looking for ? Is it just a TRUE/FALSE in say Column C for each row ?

thanks

Rob

 

[Fluff]

How about

=FILTER($D$2:$D$7,ISNUMBER(SEARCH($D$2:$D$7,A2)),"N/A")

 

[Saulos]

Hi @RobP !

Might I ask, what is the actual result you are looking for ? Is it just a TRUE/FALSE in say Column C for each row ?

Yes, of course! I am trying to identify which product (amongst the ones indicated in Column D) is being mentioned in each "Title" cell. Column C can be left empty.

Hi @Fluff !

How about
Excel Formula:
=FILTER($D$2:$D$7,ISNUMBER(SEARCH($D$2:$D$7,A2)),"N/A")

This works! Amazing!

Just one added question, in some cases the formula delivers a "#SPILL" error. I thought this happened when more than 1 value from the D Column is available in the same "Advertisement Title" cell. However, after some testing, this does not seem to be the case.

For example:

Is there any way to avoid this?

Thanks again!

 

[Fluff]

However, after some testing, this does not seem to be the case.

That is the case. B13 & B14 returns A40 and A40 TR, whilst B12 returns A40 and MixAmp pro.

What should happen in those cases?

 

[Saulos]

What should happen in those cases?

You are right!

Ideally, the more "Specific" value should be rendered.

So between A40 and A40 TR, A40 TR should be given.

 

[Fluff]

A formula does not know which value is more "specific".
Do you want to return all matches?

 

[Saulos]

Yes, all matches would be amazing also.!

 

[Fluff]

Ok, how about

=TEXTJOIN(", ",,FILTER($O$3:$O$5,ISNUMBER(SEARCH($O$3:$O$5,L3)),"N/A"))

 

[Saulos]

Ok, how about

=TEXTJOIN(", ",,FILTER($O$3:$O$5,ISNUMBER(SEARCH($O$3:$O$5,L3)),"N/A"))

This works wonders.

Thank you so much Fluff!