If 1st 3 Digits of Column A match First 3 Digits Of Column B return TRUE

[jamobe]

Hi

I have data in column A which has 10 digits. I have data in column B which has 3 digits. If the first 3 digits in column A match with column B, I would like it to return true.

I currently have this =IF(ISNUMBER(SEARCH(LEFT(A2,3),B2)),"Yes","No")

But this returns a SPILL error. I have tried using a vlookup formula with no luck, but am sure there is an easier way of being able to do this.

so for example

Column A has 1234567890

in column B there is a row with 123, and I would like this to return true in the formula.

Any help would be much appreciated. Thanks

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I see no way that formula would return a spill error. Also it's only comparing A2 to B2 not column B.
Are the values in col B actual numbers, or are they text?

 

[jamobe]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I see no way that formula would return a spill error. Also it's only comparing A2 to B2 not column B.
Are the values in col B actual numbers, or are they text?

Thanks for the heads up, I have updated my settings. I am using Office 365.

My actual formula is this =IF(ISNUMBER(SEARCH(LEFT(DE2,3),RESTRICTED_CODES)),"Yes","No")

RESTRICTED_CODES is a table which has all the 3 digit values. I have changed the format to numbers, text, general etc, but it still returns a spill error. Column DE2 I have also changed the format and no matter how much jiggling about I do it still returns a spill error. Not sure what I'm doing wrong, only thing I can think of is if this function doesn't search a column. I use this formula a lot but only when referencing to 1 cell so maybe I need to add something extra in the formula

 

[Fluff]

Thanks for that.
How about

=IF(ISNUMBER(MATCH(LEFT(DE2,3),RESTRICTED_CODES,0)),"Yes","No")

or

=IF(ISNUMBER(MATCH(LEFT(DE2,3)+0,RESTRICTED_CODES,0)),"Yes","No")

 

[jamobe]

Thanks for that.
How about

=IF(ISNUMBER(MATCH(LEFT(DE2,3),RESTRICTED_CODES,0)),"Yes","No")

or

=IF(ISNUMBER(MATCH(LEFT(DE2,3)+0,RESTRICTED_CODES,0)),"Yes","No")

1st one works perfectly! Thanks so much for your help!!

 

[Fluff]

You're welcome & thanks for the feedback.