I'll put an example below, assuming that Col A = "Old Info", Col C = "New Info", and in Col E = a formula which will check if any of the strings in Col C

*contain*info that is in Col A:

The magic formula in Col E is =IF(ISNUMBER(LOOKUP(2,1/SEARCH(A$2:A$4,C2))),"Y","N")

What I'd like to do now is tweak that formula so that the check is now for "begins with" as opposed to "contains"

Please find a table with some sample info showing the old formula and how it works:

*[And what would be great is a formula which will do the same search BUT for ""begins with"]*

i.e. The magic formula here correctly says that the New Info (in Col C) on Row 7 "contains" something in Col A [which would be ZC734]

But we would like a new formula for "begins with"...

So Row 7 would also be "N" because there is info in Col A which "starts" with BZ or BZV or BZV734 etc

Hope this helps!

Line ID | Col A: Old Info | Col C: New Info | Col E: Status i.e. does the | (this is the formula that is in E2) | ||

1 | ZVN0106B | ZV374 | Y | #=IF(ISNUMBER(LOOKUP(2,1/SEARCH(A$2:A$5,C2))),"Y","N") | ||

2 | ZV374 | ASSN01958141222 | Y | |||

3 | MC04ZE047 | SMS996D1 | N | |||

4 | SN0195814 | LT1004MH25883 | N | |||

5 | 1111ZVN0106B16 | Y | ||||

6 | 54LS00BCAJC | N | ||||

7 | BZV734 | Y |