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

New Member
Joined
Oct 10, 2023
Messages
3
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
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!
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
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")
 
Upvote 0
Hi & welcome to MrExcel.
Another option is
Excel Formula:
=IF(COUNTIFS(N:N,LEFT(G2,3)),"Yes","No")
 
Upvote 0
Solution
Not sure which formula you're talking about, but glad we could help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,224,818
Messages
6,181,152
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top