tweak a formula from "contains" to "starts with"

[ellison]

Hi, a while ago I was given (on here!) an excellent formula for helping to look up if any strings of data in a Field that "contained" strings of data in another field.

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

 

[jasonb75]

See if this does what you need,

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

It looks ok with your example but I haven't tested beyond that.

 

[ellison]

See if this does what you need,

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

It looks ok with your example but I haven't tested beyond that.

Oh my gosh that is BRILLIANT!!!!!

Huge thanks

Works a treat, lovely!!!