Writing out a list of wildcards in a formula? involves COUNTIF

[jasonbwt]

Hello excelforum,

I am learning excel on my own(mostly from here). I have tried doing a search on the question but have not found anything so far/may be looking wrongly.
I have written out a formula

Please Login or Register  to view this content.

Is there any way i can say combine all my wildcards into a list and use it in the formula without having to type it out?

I want the formula to produce an error if two or more wildcards are found in the same phrase, if not, return a keyword to be used later.
The problem is that I have a large list of words such that if the phrase contains these words then an error is produced.
I have attached a sample workbook.

Thank you very much!

Jason

[dilipandey]

Hi Jason,

It is achievable using indirect function where I have defined a dynamic name and called that in your formula using indirect function, see attached :-

sampledata(1).xlsx

Regards,
DILIPandey

<click on below 'star' if this helps>

[vlady]

maybe also like this as what i've understand..

also involves named ranges..

array formula

lista is the list of the wildcards

Please Login or Register  to view this content.

if two or more wild card is found in the cell it will return #N/A

[mikerickson]

Try this CSE formula in B2,

=INDEX(EQUIPMENTS, 1, MATCH(TRUE,COUNTIF(A2,"*"&EQUIPMENTS&"*")>0, 0))

[jasonbwt]

First of all thank you for replying.

I have tried each 3 of the methods that mike, vlady and dilip have sent but each do not seem to bring up an error if 2wildcards are found.
Am i doing something wrong?

Thanks alot!,

Jason.

[vlady]

here's my sample data..

sampledata.xlsx

sorry bout that.

[jasonbwt]

Hello, vlady.

this is exactly what i wanted.
thank you!!! i guess i needed to use an array formula.

Jason