Vlookup #value

[Pete Straman via OfficeKB.com]

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

 

[Nick Hodge]

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
(e-mail address removed)

 

[Max]

Try (untested):

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

 

[Pete Straman via OfficeKB.com]

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

Pete Straman

 

[Max]

Glad to hear that, Pete !
Thanks for the feedback ..