removing #NA from the list

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Neall - 18 Sep 2007 18:44 GMT

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

Signature

Neall

Reply to this Message

Jim Thomlinson - 18 Sep 2007 19:08 GMT

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))

Signature

HTH...

Jim Thomlinson

> 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
[quoted text clipped - 13 lines]
>
> Thanks in advance

Reply to this Message

Neall - 19 Sep 2007 16:46 GMT

Thanks, it works for removing the #N/A but it doesn't display the result if
the criteria is matched.

Any suggestions?

Signature

Neall

> Try this which checks for a match prior to doing the lookup...
>
[quoted text clipped - 18 lines]
> >
> > Thanks in advance

Reply to this Message