Checking Numbers Between Columns

Nanaia

Active Member
Joined
Jan 11, 2018
Messages
304
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
I'm trying to check the first four numbers found in column A and see if they are located anywhere in column O. I've tried VLOOKUP with LEFT but cannot seem to get the it to work. It always results in an #NA error. My goal is; if the first four numbers is not found anywhere in column O, I would like the result of the formula to display the numbers that were not found in column O. i.e. if column A has 1234 as the first four numbers and it is not found anywhere in column O, the result of the formula should display 1234.

This is the formula I've tried
[ =VLOOKUP(VALUE(LEFT(A2,4)),LEFT($O$2:$O$500,4),2,FALSE) ]

Thank you in advance for your assistance.
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Something like =IF(isnumber(search(left(a2,4),o2:eek:500),"TRUE",left(a2,4))
 
Upvote 0
Thank you for the suggestion. That formula is giving listing numbers when it shouldn't. If there are letters after the first four numbers in a cell does it change the formatting of the cell to something other than "NUMBER"? Because if it does, then I need to modify things because both columns have a combination of letters and numbers. It's just the first four numbers I am interested in though.
 
Upvote 0
I tried removing ISNUMBER and it isn't flagging the appropriate numbers that it needs to flag and it gives a #VALUE ! error instead of pulling the first four numbers from the cell in column A. Is there a better way for me to do this other than visually line the columns up side by side and go through them visually?
 
Upvote 0
Could you give some examples of what is to be looked up on what you expect as result?
 
Upvote 0
I'll try...

Cannot include image of data - site won't allow me to copy data. This is three columns from the spreadsheet:

Col A
Col O
Col Z
1111
1111_25
TRUE
1111_N1111_25_N
TRUE
1112 1112_25
TRUE
1112_N1112_25_NTRUE
1113 1113_25_N
TRUE
1113_N1115_25
1113
1115 1115_25_N
TRUE
1115_N1116_25
TRUE
1116 1116_25_N
TRUE
1116_N1121_25
TRUE
1121 1121_25_N
TRUE
1121_N1125_25
TRUE
1122 1125_25_N
1122
1122_N1126_25
1122_N
1123 1131_25
1123
1123_N1131_25_N
1123_N
1125 1132_25
TRUE

<colgroup><col><col><col></colgroup><tbody>
</tbody>

Column A is the master list for programs. Column O is the column to be checked against the master list. The 25 is irrelevant for checking purposes which is why I wanted to only check the first four digits. Column Z tells us what program from column A is missing from column Z so we know a new program needs to be created. The N can be included in column Z but isn't required.
 
Last edited:
Upvote 0
Got it. I think. Works so far anyway.

[ =IF(ISERROR(VLOOKUP(A2,$O$2:$O$1001,1,FALSE)),A2,TRUE) ]
 
Last edited:
Upvote 0
Spoke too soon. Still doesn't work. Can't get it to narrow to the first four digits.
 
Upvote 0
How about
=IF(ISERROR(VLOOKUP(LEFT(A2,4),LEFT($O$2:$O$1001,4),1,FALSE)),A2,TRUE)

This is an array formula & needs to be confirmed with Ctrl Shift Enter, not just Enter
 
Upvote 0
I forgot that arrays needed to be treated differently. Thank you!
Ok, weird results. Why would that formula show some missing numbers but not others?
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

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