Need to create a formula looking at the 1st 3 digits a 10-digit number in cells (Column G2) that match 3 digits of Column N

[jfram]

I'm trying to identify the first 3 digits of the cells in Column G with the list of 3-digit numbers (array) in Column N. I've tried the following formula but it only returns "No" results in every cell:

=IF(ISNUMBER(MATCH(LEFT(G2,3),N2:N72,0)),"Yes","No")

EX.
COL G COL N COL with EXPECTED RESULT
123456789 987 No
987654321 247 Yes
456789123 248 No
321654987 249 Yes
250
321

I've also tried creating a table for Column N, but the result was the same. I also tried to format the data in Column G and N as both numbers, text and general. Same results. Getting frustrated. Can you assist?

Thanks so much!

 

[Scott T]

When you use left it turns the number into text. when you match text 987 to number 987 it does not match. by doing a math operation you can turn it back into a number

=IF(ISNUMBER(MATCH(LEFT(G2,3)+0,N2:N72,0)),"Yes","No")

 

[Fluff]

Hi & welcome to MrExcel.
Another option is

=IF(COUNTIFS(N:N,LEFT(G2,3)),"Yes","No")

 

[jfram]

Worked perfectly. Thank you very much!!!

 

[Fluff]

Not sure which formula you're talking about, but glad we could help & thanks for the feedback.

 

[Scott Huish]

Hi & welcome to MrExcel.
Another option is

=IF(COUNTIFS(N:N,LEFT(G2,3)),"Yes","No")

I think COUNTIF should suffice though.

 

[jfram]

Worked perfectly. Thank you very much!!!

Just a quick note...I had to change (N:N) to a table because that data was fixed, not variable.