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

#### ellison

##### Board Regular
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

### Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

#### jasonb75

##### Well-known Member
See if this does what you need,
Excel Formula:
``=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

##### Board Regular
See if this does what you need,
Excel Formula:
``=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!!!

Replies
6
Views
368
Replies
4
Views
242
Replies
4
Views
92
Replies
1
Views
108
Replies
3
Views
318