Checking Numbers Between Columns

[Nanaia]

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.

 

[arthurbr]

Something like =IF(isnumber(search(left(a2,4),o2500),"TRUE",left(a2,4))

 

[Nanaia]

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.

 

[Nanaia]

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?

 

[arthurbr]

Could you give some examples of what is to be looked up on what you expect as result?

 

[Nanaia]

I'll try...

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

[TABLE="width: 201"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Col A
[/TD]
[TD]Col O
[/TD]
[TD]Col Z
[/TD]
[/TR]
[TR]
[TD="align: right"]1111
[/TD]
[TD] 1111_25
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]1111_N[/TD]
[TD]1111_25_N
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]1112[/TD]
[TD] 1112_25
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]1112_N[/TD]
[TD]1112_25_N[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]1113[/TD]
[TD] 1113_25_N
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]1113_N[/TD]
[TD]1115_25
[/TD]
[TD="align: right"]1113[/TD]
[/TR]
[TR]
[TD="align: right"]1115[/TD]
[TD] 1115_25_N
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]1115_N[/TD]
[TD]1116_25
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]1116[/TD]
[TD] 1116_25_N
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]1116_N[/TD]
[TD]1121_25
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]1121[/TD]
[TD] 1121_25_N
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD]1121_N[/TD]
[TD]1125_25
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
[TR]
[TD="align: right"]1122[/TD]
[TD] 1125_25_N
[/TD]
[TD="align: right"]1122[/TD]
[/TR]
[TR]
[TD]1122_N[/TD]
[TD]1126_25
[/TD]
[TD]1122_N[/TD]
[/TR]
[TR]
[TD="align: right"]1123[/TD]
[TD] 1131_25
[/TD]
[TD="align: right"]1123[/TD]
[/TR]
[TR]
[TD]1123_N[/TD]
[TD]1131_25_N
[/TD]
[TD]1123_N[/TD]
[/TR]
[TR]
[TD="align: right"]1125[/TD]
[TD] 1132_25
[/TD]
[TD="align: center"]TRUE[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[Nanaia]

Got it. I think. Works so far anyway.

[ =IF(ISERROR(VLOOKUP(A2,$O$2:$O$1001,1,FALSE)),A2,TRUE) ]

 

[Nanaia]

Spoke too soon. Still doesn't work. Can't get it to narrow to the first four digits.

 

[Fluff]

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

 

[Nanaia]

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?