Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP error
I am trying to use a VLOOKUP from a large set of data to a smaller set of
data (the array does not contain all of the values being looked up), and thus returns #N/A. It works for the first value which is in the table, but as soon as there is a value missing, all other returns are #N/A as well. Is there any way to get around these absent values? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP error
You can display a standard value for not found entries, using an IF statament:
=IF(ISNA(VLOOKUP(...),"Value not found",VLOOKUP(...)) Also, check the four parameter of VLOOKUP, if you don't have the list sorted it is better to use FALSE there. Hope this helps, Miguel. "Mr. Boyer" wrote: I am trying to use a VLOOKUP from a large set of data to a smaller set of data (the array does not contain all of the values being looked up), and thus returns #N/A. It works for the first value which is in the table, but as soon as there is a value missing, all other returns are #N/A as well. Is there any way to get around these absent values? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP error
Look at the function ISERROR to be used as an error handler around the
VLOOKUP...do something as follows: =IF(ISERROR(VLOOKUP(___,____,___,___)),"",VLOOKUP( ___,____,___,___)) what this will do is first attempt to evaluate the VLOOKP, if an error results, it will post a null ("") in the cell, if it was successful, it will reevaluate the VLOOKUP and post the desired result. "Mr. Boyer" <Mr. wrote in message ... I am trying to use a VLOOKUP from a large set of data to a smaller set of data (the array does not contain all of the values being looked up), and thus returns #N/A. It works for the first value which is in the table, but as soon as there is a value missing, all other returns are #N/A as well. Is there any way to get around these absent values? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP error
Thanks! both were very helpful, and TIMELY!!!!!!
"Larry S" wrote: Look at the function ISERROR to be used as an error handler around the VLOOKUP...do something as follows: =IF(ISERROR(VLOOKUP(___,____,___,___)),"",VLOOKUP( ___,____,___,___)) what this will do is first attempt to evaluate the VLOOKP, if an error results, it will post a null ("") in the cell, if it was successful, it will reevaluate the VLOOKUP and post the desired result. "Mr. Boyer" <Mr. wrote in message ... I am trying to use a VLOOKUP from a large set of data to a smaller set of data (the array does not contain all of the values being looked up), and thus returns #N/A. It works for the first value which is in the table, but as soon as there is a value missing, all other returns are #N/A as well. Is there any way to get around these absent values? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
REF# error using VLOOKUP | Excel Worksheet Functions | |||
vlookup error - recognition of value | Excel Worksheet Functions | |||
Fill down error for Vlookup | Excel Worksheet Functions | |||
vlookup weird error | Excel Worksheet Functions | |||
vlookup error | Excel Discussion (Misc queries) |