Vlookup #value
 

Hey!

I need to get this Vlookup statement to write a zero if it returns the #n/a
or #value! I am catching the #n/a but not the #value! with my statement.

Can you help me out? If you know what the integer designation is for #VALUE!
that would work for me.

=IF(ISNA(VLOOKUP(A6,'AR Gross Up'!A:C,3,FALSE))=TRUE, 0, -VLOOKUP(A6,'AR
Gross Up'!A:C,3,FALSE)) - can't catch the #value - need to put 0 in the
cell when #value occurs

--
Message posted via http://www.officekb.com

Pete

Use the ISERROR function in place of the ISNA one. (You also don't need the
=TRUE)

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
HIS


"Pete Straman via OfficeKB.com" wrote in message
...
Hey!

I need to get this Vlookup statement to write a zero if it returns the
#n/a
or #value! I am catching the #n/a but not the #value! with my statement.

Can you help me out? If you know what the integer designation is for
#VALUE!
that would work for me.

=IF(ISNA(VLOOKUP(A6,'AR Gross Up'!A:C,3,FALSE))=TRUE, 0, -VLOOKUP(A6,'AR
Gross Up'!A:C,3,FALSE)) - can't catch the #value - need to put 0 in the
cell when #value occurs

--
Message posted via http://www.officekb.com

Try (untested):

=IF(ISERROR(VLOOKUP(A6,'AR Gross Up'!A:C,3,FALSE)),0,-VLOOKUP(A6,'AR Gross
Up'!A:C,3,FALSE))

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Pete Straman via OfficeKB.com" wrote in message
...
Hey!

I need to get this Vlookup statement to write a zero if it returns the
#n/a
or #value! I am catching the #n/a but not the #value! with my statement.

Can you help me out? If you know what the integer designation is for
#VALUE!
that would work for me.

=IF(ISNA(VLOOKUP(A6,'AR Gross Up'!A:C,3,FALSE))=TRUE, 0, -VLOOKUP(A6,'AR
Gross Up'!A:C,3,FALSE)) - can't catch the #value - need to put 0 in the
cell when #value occurs

--
Message posted via http://www.officekb.com

I finally figured it out which turned out to be exactly as you recommended.
Thank you for your help.

Pete Straman

--
Message posted via http://www.officekb.com

Glad to hear that, Pete !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Pete Straman via OfficeKB.com" wrote in message
...
I finally figured it out which turned out to be exactly as you
recommended.
Thank you for your help.

Pete Straman

--
Message posted via http://www.officekb.com