How can I use an IFISNUMBER formula in conjunction with Vlookup?

[Pete_UK]

Post some more details of what you want to achieve.

In the meantime, here's an example:

=IF(ISNUMBER(A1),VLOOKUP(A1,table,2,0),"")

This will perform the lookup only if A1 is a number, otherwise it will
return a blank.

Hope this helps.

Pete

 

[Dan]

Pete,

I am trying to avoid a return of #NA. I am using the V-Look up function to
pull a specific number from another file. When the number I am looknig for
does not exist, the V-Look Up returns a #NA. Because of this I cannot sum the
data for a grand total.

I want to us a IFISNUMBER formula to return a 0 if the V-look up does not
return a value.

Could you help me with this?

Dan

 

[Spiky]

=IF(ISNUMBER(VLOOKUP(A1,table,2,0),VLOOKUP(A1,table,2,0),0)

or

=IF(ISERROR(VLOOKUP(A1,table,2,0),0,VLOOKUP(A1,table,2,0))

 

[Dan]

This formula keeps giving me an error

=IF(ISNUMBER(VLOOKUP(A9,'F:\Dan\2008 Office Supplies\[2008-12.txt]2008-12
(2)'!$A$2:$G$234,6,FALSE),VLOOKUP(A9,'F:\Dan\2008 Office
Supplies\[2008-12.txt]2008-12 (2)'!$A$2:$G$234,6,FALSE),0)

 

[Shane Devenshire]

Hi,

Try

=IF(ISNA(VLOOKUP(A9,'F:\Dan\2008 Office Supplies\[2008-12.txt]2008-12
(2)'!$A$2:$G$234,6,FALSE)),VLOOKUP(A9,'F:\Dan\2008 Office
Supplies\[2008-12.txt]2008-12 (2)'!$A$2:$G$234,6,FALSE),0)

In 2007

=IFERROR(VLOOKUP(A9,'F:\Dan\2008 Office Supplies\[2008-12.txt]2008-12
(2)'!$A$2:$G$234,6,FALSE),0)

 

[Pete_UK]

You've missed a bracket - try it this way:

=IF(ISNUMBER(VLOOKUP(A9,'F:\Dan\2008 Office Supplies\[2008-12.txt]
2008-12 (2)'!$A$2:$G$234,6,FALSE)),VLOOKUP(A9,'F:\Dan\2008 Office
Supplies\[2008-12.txt]2008-12 (2)'!$A$2:$G$234,6,FALSE),0)

Personally, I prefer to use ISNA, like this:

=IF(ISNA(VLOOKUP(A9,'F:\Dan\2008 Office Supplies\[2008-12.txt]2008-12
(2)'!$A$2:$G$234,6,FALSE)),0,VLOOKUP(A9,'F:\Dan\2008 Office Supplies
\[2008-12.txt]2008-12 (2)'!$A$2:$G$234,6,FALSE))

Hope this helps.

Pete

This formula keeps giving me an error

=IF(ISNUMBER(VLOOKUP(A9,'F:\Dan\2008 Office Supplies\[2008-12.txt]2008-12
(2)'!$A$2:$G$234,6,FALSE),VLOOKUP(A9,'F:\Dan\2008 Office
Supplies\[2008-12.txt]2008-12 (2)'!$A$2:$G$234,6,FALSE),0)

=IF(ISNUMBER(VLOOKUP(A1,table,2,0),VLOOKUP(A1,table,2,0),0)

=IF(ISERROR(VLOOKUP(A1,table,2,0),0,VLOOKUP(A1,table,2,0))- Hide quoted text -

- Show quoted text -