Vlookup question -- removing bad numbers
 

I have a list of 3,000 loan account numbers -- each one has a value
for an "NAICS" Code. An NAICS Code is a 6-digit code that classifies
loans by industry -- there are over 1,100 of these codes ranging from
111110-Soybean Farming to 928120-International Affairs.

Here is my dilemma -- some of the loans have a value of 0, most of
them have a code that exists, but a few have codes that don't even
exist.

I have a complete list of all of the codes and their descriptions in 2
columns, and a list of all of our loans with their codes listed. I
want to create a column that displays the NAICS code description by
the loan, as well.

I have sorted all of the loans with their NAICS code in descending
order and tried the vlookup function, but it returns a value even if
the code doesn't really exist. I want to be able to identify the
loans that have a code that doesn't exist.

I'll use the following example to show what I would like:

Loan# NAICS Code NAICS Description
1 0 Not an actual code
2 111110 Soybean Farming
3 111111 Not an actual code
4 928120 International Affairs


Here is an example of the Dataset or Range that I will call
"NAICS" (remember that there are actually over 1,100 entries):
NAICS Code Description
111110 Soybean Farming
111120 Oilseed Farming
...........
928120 International Affairs



It's probably simple, but I can't find anything in any help or forum.

Vlookup question -- removing bad numbers
 

VLookup should work. The last node must be FALSE so it will not return
something unless it gets an exact match. Ex:
=IF(ISNA(VLOOKUP(B2,NAICS_List,2,FALSE)),"Not an actual
code",VLOOKUP(B2,NAICS_List,2,FALSE)) will display as you indicated you want
it, or you can just do the VLookup and return #N/A.
--
Good Luck,
Peg


"kwstroud" wrote:

I have a list of 3,000 loan account numbers -- each one has a value
for an "NAICS" Code. An NAICS Code is a 6-digit code that classifies
loans by industry -- there are over 1,100 of these codes ranging from
111110-Soybean Farming to 928120-International Affairs.

Here is my dilemma -- some of the loans have a value of 0, most of
them have a code that exists, but a few have codes that don't even
exist.

I have a complete list of all of the codes and their descriptions in 2
columns, and a list of all of our loans with their codes listed. I
want to create a column that displays the NAICS code description by
the loan, as well.

I have sorted all of the loans with their NAICS code in descending
order and tried the vlookup function, but it returns a value even if
the code doesn't really exist. I want to be able to identify the
loans that have a code that doesn't exist.

I'll use the following example to show what I would like:

Loan# NAICS Code NAICS Description
1 0 Not an actual code
2 111110 Soybean Farming
3 111111 Not an actual code
4 928120 International Affairs


Here is an example of the Dataset or Range that I will call
"NAICS" (remember that there are actually over 1,100 entries):
NAICS Code Description
111110 Soybean Farming
111120 Oilseed Farming
...........
928120 International Affairs



It's probably simple, but I can't find anything in any help or forum.

Vlookup question -- removing bad numbers
 

Worked like a charm! I copied and did a "Paste Special" (values only)
and was able to sort out all the input errors. Thanks so much!