Excel - How to see if a value starts with a value from a different column

Question

I am trying to see if a value in one column starts with one of the values from another column. ie.

col A        col B          col C
Hel          Good Bye       YES
Go           Go Now         YES
By           Stuff          NO
Something    etc.           etc.

Where Col C is the Forumla to see if col B's value starts with any value from col A and outputs yes or no. In this case the Good Bye and Go Now both start with Go and hence are considered a match.

Thanks, DMan

Answer 1

Use this one in C2:

=IF(MAX(COUNTIF(B2, $A$2:$A$100 & "*")),"YES", "NO")

with array entry (press CTRL+SHIFT+ENTER to evaluate it).

Formula above assumes that there is no empty cells in range A2:A100. If it's not true and there are empty cells in A2:A100, use this one:

=IF(MAX(COUNTIF(B2,IF($A$2:$A$100<>"",$A$2:$A$100 & "*"))),"YES", "NO")

with array entry.

---

Also this one should be sligtly faster for big ranges:

=IF(OR(ISNUMBER(MATCH(IF($A$2:$A$100<>"",$A$2:$A$100 & "*"),B2,0))),"YES","NO")

with array entry.