Find partial text in a range of data

[daniels012]

Is there a formula I can create (Array formula probably), that will give me a true if text is found.
I need to have a yes/ no if a name is found in a certain range.

Look in the E4:08 range and see if you find the name from cell Q4

In the Range of cells you might have
Ford/Knight or
Davis/Franklin

In cell Q4 the name would be "Ford"'
So not only does it need to find it in the range, but it has to find it in a cell in the range as well.
I am fine with a sort an Array formula using Countif somehow, just to give true or false.

Any help is greatly appreciated.

Michael D

 

[lenze]

Would a VBA solution work for you???
lenze

 

[VoG]

Try

=COUNTIF(E4:O8,"*"&Q4&"*")>0

 

[daniels012]

Lenze,
No I really need a formula.

Vog,
Wow! That seems to be doing the trick!
Thank You,
MIchael

 

[lenze]

The reason I suggested VBA is that it can be used to return the address of the
cell(s) where Q4 is found!!
lenze

 

[daniels012]

Ah, I gotcha.
No, I just need to know if it was there somewhere.

Thank You,
Michael

 

[BigBuilder]

Hi,
Thanks very much for this formula (=COUNTIF(E4:O8,"*"&Q4&"*")>0). It, (obviously), worked well for me.

For reasons I won't bore anyone with, I 'fiddled' about with it t try and get it to run as an array formula and came up with: =SUM(IF(C5:C8="*"&LEFT(L5,SEARCH(" ",L5))&"*",1,0)). (With braces around it, of course !). It doesn't work. It returns a zero, yet the COUNTIF formula works perfectly.

The LEFT business is because I don't actually know what text string I'm searching for i.e., the search string isn't fixed - only that I know the partial search string I'm searching for occurs in the characters before the first space - but, I think, (could be wrong), that this isn't necessarily to do with the reason why my array formula iteration isn't working.
Any insights appreciated.

Thanks.

 

[BigBuilder]

Hi, again,
Some more digging around, (good 'ole Google !), has revealed the answer to my query above to be quite simple: you can't use wildcards in an array formula as the array formula takes the wildcard character as a literal and incoporates it into the search text string. So, e.g., it'd go searching for "*Aintree *", and my search criteria wants it to search for "any character,Aintree, space, any character.
Apologies to those knowlegeable people on this forum who already knew that but there may be the casual viewer of this forum who didn't, (like me !).