@ DonkeyOte
Neat "trick", adding the leading and trailing spaces to the strings, saving the array as used by Ron
Would using a Dynamic Named Range be a significant advantage with either formula?
ModelNo
Refers to:
=Sheet2!$A$2:INDEX(Sheet2!$A:$A,COUNTA(Sheet2!$A:$A))
Then either
=IF(A2="","",SUMPRODUCT(COUNTIF(A2,{"","* ","* "}&ModelNo&{" *"," *",""}))>0)
or
=IF($A2="","",ISNUMBER(LOOKUP(9.99E+307,SEARCH(" "&ModelNo&" "," "&$A2&" "))))
I can see it being an advantage with sumproduct, calculation time wise.
Bookmarks