Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing #NA from the list
I am pulling in data from a DB, and in one of the rows I am using a VLOOKUP
which works perfectly, however with the expectation of added growth and to not have to worry about adding this syntax later on in the project I have cells that have no data in them the the VLOOKUP uses, so the results are showing #N/A Is there anyway I can make it so those results do not show? here is the syntaxt =VLOOKUP(YYY!B17,'XX-XX'!A17:F166,2,FALSE) the cell where the VLOOKUP resides that does not have data in the cells it is looking up comes back with a #N/A display on the spreadsheet and its not "purdy" Thanks in advance -- Neall |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing #NA from the list
Try this which checks for a match prior to doing the lookup...
=if(countif('XX-XX'!A17:A166, YYY!B17) = 0, "", VLOOKUP(YYY!B17,'XX-XX'!A17:F166,2,FALSE)) -- HTH... Jim Thomlinson "Neall" wrote: I am pulling in data from a DB, and in one of the rows I am using a VLOOKUP which works perfectly, however with the expectation of added growth and to not have to worry about adding this syntax later on in the project I have cells that have no data in them the the VLOOKUP uses, so the results are showing #N/A Is there anyway I can make it so those results do not show? here is the syntaxt =VLOOKUP(YYY!B17,'XX-XX'!A17:F166,2,FALSE) the cell where the VLOOKUP resides that does not have data in the cells it is looking up comes back with a #N/A display on the spreadsheet and its not "purdy" Thanks in advance -- Neall |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
removing #NA from the list
Thanks, it works for removing the #N/A but it doesn't display the result if
the criteria is matched. Any suggestions? -- Neall "Jim Thomlinson" wrote: Try this which checks for a match prior to doing the lookup... =if(countif('XX-XX'!A17:A166, YYY!B17) = 0, "", VLOOKUP(YYY!B17,'XX-XX'!A17:F166,2,FALSE)) -- HTH... Jim Thomlinson "Neall" wrote: I am pulling in data from a DB, and in one of the rows I am using a VLOOKUP which works perfectly, however with the expectation of added growth and to not have to worry about adding this syntax later on in the project I have cells that have no data in them the the VLOOKUP uses, so the results are showing #N/A Is there anyway I can make it so those results do not show? here is the syntaxt =VLOOKUP(YYY!B17,'XX-XX'!A17:F166,2,FALSE) the cell where the VLOOKUP resides that does not have data in the cells it is looking up comes back with a #N/A display on the spreadsheet and its not "purdy" Thanks in advance -- Neall |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Removing Both Duplicates from a list | Excel Discussion (Misc queries) | |||
Removing List from Cells | Excel Discussion (Misc queries) | |||
Removing a reference from the LIST | Excel Programming | |||
Removing all entries in one list that appear in a different list | Excel Discussion (Misc queries) | |||
removing duplicates from a list | Excel Discussion (Misc queries) |