| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Stumped: Find first match in list then reference dif cell in row.. | Excel Discussion (Misc queries) | |||
| Find a match in a list | Excel Discussion (Misc queries) | |||
| Find closest text match for each unique entry in a list | Excel Discussion (Misc queries) | |||
| Using the MATCH formula and list the duplicates | Excel Worksheet Functions | |||
| list 1 has 400 names List 2 has 4000. find manes from list 1 on 2 | Excel Worksheet Functions | |||
Hybrid Mode
