Help Vlook up returning N?A

[andy]

We are using the following formula and it returns N/A
=IF($A79>0,IF(VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE)="","",VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE)),"")

How do i use isna function with this formula

 

[Roger Govier]

Hi Andy

Try
=IF($A79>0,
IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),""
,VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,FALSE)),"")

 

[Bernie Deitrick]

Andy,

It is returning NA because it didn't find the value from cell A79.

Generally, wrap your first check in an ISERROR function:

=IF($A79>0,IF(ISERROR(VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE)),"",VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE)),"")

HTH,
Bernie
MS Excel MVP

 

[Teethless mama]

=IF($A79>0, IF(COUNTIF('BO Output'!$A$3:$A$2002,$A79),VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE),""))

 

[Mark]

In some cases it is returning <blank>, as desired, and in other cases it
returns "0". Does anyone know why??

 

[Pete_UK]

You might have an empty cell in your table which is being found
correctly, but Excel returns it as zero rather than "".

Hope this helps.

Pete

 

[Mark]

The ISNA is working fine - it's the vlookup. I am using this:
=VLOOKUP($A8,'Plan & Track'!$A$3:$AN$2002,9,FALSE)
I am using vlookups on two worksheets - it returns "0" from one worksheet,
and <blank> from the other. I don't see any differences in the cells I am
trying to find.
Any ideas?

In some cases it is returning <blank>, as desired, and in other cases it
returns "0". Does anyone know why??

 

[Mark]

yes, it is finding the cell - if I plug in a value it works fine. How can I
force it to find a blank instead of a 0? Do I need to format the cells?

 

[Pete_UK]

You could apply conditional formatting to the cell such that if the
content is 0 then use a foreground colour of white (which on a white
background will make the cell appear blank).

Hope this helps.

Pete

 

[Mark]

good idea, but - we are uploading the result into a db2 table, so we don't
want zeroes. Also, some of the legitimate data is "0".

 

[Pete_UK]

If zero is a legitimite value, why do you want to get rid of it?

You could wrap what you have already in a formula along the lines of:

=IF(existing_formula=0,"",existing_formula)

to make sure that a blank is returned instead of zero, but your
formula will become unwieldy. If you are using the version that Roger
posted earlier, then you could amend it to this:

=IF($A79<=0,"",IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P
$2002,4,0)),"",IF(VLOOKUP($A79,'BO Output'!$A$3:$P
$2002,4,0)=0,"",VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,0))))

which is slightly less cumbersome.

Hope this helps.

Pete

 

[Mark]

Thanks for the effort, but that did not work, either. The function is
returning zeroes for all blanks, and also returning a zero for a zero value.
This version blanks out the #N/As, which is good, but it also strips out all
of the legitimate zeroes.

 

[Pete_UK]

So, you want zero to be returned if that is what is in the table, but
if the table entry is blank then you want a blank to be returned? If
so, try this modification:

=IF($A79<=0,"",IF(ISNA(VLOOKUP($A79,'BO Output'!$A$3:$P
$2002,4,0)),"",IF(VLOOKUP($A79,'BO Output'!$A$3:$P
$2002,4,0)="","",VLOOKUP($A79,'BO Output'!$A$3:$P$2002,4,0))))

Hope this helps.

Pete

 

[andy]

Pete_UK thanks for the help, mark's a coworker and i have new spreadsheet
to use! You rock!

 

[andy]

Thanks for your reply!
--
andy

Andy,

It is returning NA because it didn't find the value from cell A79.

Generally, wrap your first check in an ISERROR function:

=IF($A79>0,IF(ISERROR(VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE)),"",VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE)),"")

HTH,
Bernie
MS Excel MVP

 

[andy]

thanks for your reply.
--
andy

=IF($A79>0, IF(COUNTIF('BO Output'!$A$3:$A$2002,$A79),VLOOKUP($A79,'BO
Output'!$A$3:$P$2002,4,FALSE),""))

 

[Pete_UK]

Well, thanks for feeding back, Andy.

Pete

Pete_UK thanks for the help,  mark's a coworker and i have  new spreadsheet
to use!  You rock!  
--
andy






- Show quoted text -