Checking Numbers Between Columns

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
210
Office Version
365
Platform
Windows, 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.
 

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
1,926
Something like =IF(isnumber(search(left(a2,4),o2:eek:500),"TRUE",left(a2,4))
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
210
Office Version
365
Platform
Windows, MacOS
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

Board Regular
Joined
Jan 11, 2018
Messages
210
Office Version
365
Platform
Windows, MacOS
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

Well-known Member
Joined
Dec 8, 2006
Messages
1,926
Could you give some examples of what is to be looked up on what you expect as result?
 

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
210
Office Version
365
Platform
Windows, MacOS
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:

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
210
Office Version
365
Platform
Windows, MacOS
Got it. I think. Works so far anyway.

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

Nanaia

Board Regular
Joined
Jan 11, 2018
Messages
210
Office Version
365
Platform
Windows, MacOS
Spoke too soon. Still doesn't work. Can't get it to narrow to the first four digits.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
33,967
Office Version
365
Platform
Windows
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

Board Regular
Joined
Jan 11, 2018
Messages
210
Office Version
365
Platform
Windows, MacOS
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:

Forum statistics

Threads
1,086,122
Messages
5,387,973
Members
402,091
Latest member
thomastsiakis

Some videos you may like

This Week's Hot Topics

Top