formula with wildcard

[excellentexcel]

hi guys
thanks to nbvc i have this formula

Please Login or Register  to view this content.

but the trouble is i need it to understand there may be wildcards

ie

input sheet g1 has the word "york" in it ( without the quotes )

however the results in columns d and h
may suggest "york city"

[DonkeyOte]

in which case use SEARCH

=SUMPRODUCT(--(ISNUMBER(SEARCH('input sheet'!$G1,'head to head'!$D$1:$D1000))),--(ISNUMBER(SEARCH('input sheet'!$G1,'head to head'!$H$1:$H1000))))

NOTE:

I revised D:D to D1:D1000 and H:H to H1:H1000 ... try to restrict the ranges you are working with else performance will be affected.
Also if you were to try and run the entire column reference version pre XL07 you would get an error (I know you're running 2007 but worth a mention for others)

[excellentexcel]

thanks donkeyote
that all makes sense
would it make it entirely better if i narrowed it down further to just the rows with data?

[DonkeyOte]

In retrospect assuming your lookup value is always a complete word -- ie Arsenal and not **** for Arsenal & Tottenham rather than Tott for Tottenham Hotspur then the below is a little more robust:

=SUMPRODUCT(--(ISNUMBER(SEARCH('input sheet'!$G1&" ",'head to head'!$D$1:$D1000&" "))),--(ISNUMBER(SEARCH('input sheet'!$G1&" ",'head to head'!$H$1:$H1000&" "))))

It adds a space to the G1 value and does likewise for all the values in the lookup columns.. in doing so it will ensure it only looks for "York " ... so if you had say a team called "Yorksters" when searching "York" for York City "Yorksters" would be ignored... ie it searches for "York " ... "Yorksters" does not meet that criteria whereas "York City" does...

Hopefully that makes some sense.

[DonkeyOte]

In relation to your other thread... given you're using 2007 you can dispense with Sumproduct altogether and use COUNTIFS (quicker)

=COUNTIFS('head to head'!$D$1:$D$1000,'input sheet'!$G1&" *",'head to head'!$H$1:$H$1000,'input sheet'!$G1&" *")

Note: COUNTIFS is not backwards compatible with earlier versions of Excel.