Formula to find a value in a list that doesnt match another list
 

I have a data page with 8000 entries, one column in those entries is a
location in just numbers. I have a lookup table referenced to so that I can
change those numbers into a location name. If someone adds a new number I
will know and the lookup function will just go to the next lowest value and
name it to that area. I am trying to figure out a way that a formula would
tell me that someone has added a new number. Any ideas? Thanks.

Formula to find a value in a list that doesnt match another list
 

Without your actual formula, we can only guess.....

It seems like you're using the VLOOKUP function without forcing it to find
exact matches by setting the 4th parameter to 0.

A formula like this will return an error if there is no exact match:
=VLOOKUP(loc_num, num_name_list, 2,0)

This variation will flag mis-matches:
=IF(ISNA(VLOOKUP(loc_num, num_name_list, 2,0)),"INVALID",VLOOKUP(loc_num,
num_name_list, 2,0))

An alternative might be to use Data Validation in the location number column
so the users can only select valid numbers from the list. Debra Dalgleish
has DV instructions at her website:
http://www.contextures.com/xlDataVal01.html

Note: Either way you'll need to only return exact matches in the VLOOKUP
formula.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Josh Johansen" wrote:

I have a data page with 8000 entries, one column in those entries is a
location in just numbers. I have a lookup table referenced to so that I can
change those numbers into a location name. If someone adds a new number I
will know and the lookup function will just go to the next lowest value and
name it to that area. I am trying to figure out a way that a formula would
tell me that someone has added a new number. Any ideas? Thanks.

Formula to find a value in a list that doesnt match another li
 

Ron, that was exactly the info I needed, thanks so much.

"Ron Coderre" wrote:

Without your actual formula, we can only guess.....

It seems like you're using the VLOOKUP function without forcing it to find
exact matches by setting the 4th parameter to 0.

A formula like this will return an error if there is no exact match:
=VLOOKUP(loc_num, num_name_list, 2,0)

This variation will flag mis-matches:
=IF(ISNA(VLOOKUP(loc_num, num_name_list, 2,0)),"INVALID",VLOOKUP(loc_num,
num_name_list, 2,0))

An alternative might be to use Data Validation in the location number column
so the users can only select valid numbers from the list. Debra Dalgleish
has DV instructions at her website:
http://www.contextures.com/xlDataVal01.html

Note: Either way you'll need to only return exact matches in the VLOOKUP
formula.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Josh Johansen" wrote:

I have a data page with 8000 entries, one column in those entries is a
location in just numbers. I have a lookup table referenced to so that I can
change those numbers into a location name. If someone adds a new number I
will know and the lookup function will just go to the next lowest value and
name it to that area. I am trying to figure out a way that a formula would
tell me that someone has added a new number. Any ideas? Thanks.

Formula to find a value in a list that doesnt match another li
 

You're very welcome, Josh....and thanks for the feedback.

***********
Regards,
Ron

XL2002, WinXP


"Josh Johansen" wrote:

Ron, that was exactly the info I needed, thanks so much.

"Ron Coderre" wrote:

Without your actual formula, we can only guess.....

It seems like you're using the VLOOKUP function without forcing it to find
exact matches by setting the 4th parameter to 0.

A formula like this will return an error if there is no exact match:
=VLOOKUP(loc_num, num_name_list, 2,0)

This variation will flag mis-matches:
=IF(ISNA(VLOOKUP(loc_num, num_name_list, 2,0)),"INVALID",VLOOKUP(loc_num,
num_name_list, 2,0))

An alternative might be to use Data Validation in the location number column
so the users can only select valid numbers from the list. Debra Dalgleish
has DV instructions at her website:
http://www.contextures.com/xlDataVal01.html

Note: Either way you'll need to only return exact matches in the VLOOKUP
formula.


Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Josh Johansen" wrote:

I have a data page with 8000 entries, one column in those entries is a
location in just numbers. I have a lookup table referenced to so that I can
change those numbers into a location name. If someone adds a new number I
will know and the lookup function will just go to the next lowest value and
name it to that area. I am trying to figure out a way that a formula would
tell me that someone has added a new number. Any ideas? Thanks.