VLOOKUP ERROR

[Guest]

I started out with =VLOOKUP(F12,CSSP!A10:F2000,4,FALSE)
the output returns #NA if there is nothing found

The following statement works great if there is an error.
=IF(ISERROR(VLOOKUP(F12,CSSP!A10:F2008,4,FALSE)),"0.00")

The problem I am having is if there is actually a good value returned I get
the following value in the cell "FALSE"

I am doing some calculations on this cell after a value is returned

Can anyone help me with this.

 

[Guest]

The problem is you didn't finish your IF statement. You told it what you
wanted to do if there was an error, but not what to do if there isn't an
error.

=IF(ISERROR(VLOOKUP(F12,CSSP!A10:F2008,4,FALSE)),"0.00",VLOOKUP(F12,CSSP!A10:F2008,4,FALSE))

Also, you might want to change your vlookup to
VLOOKUP(F12,CSSP!$A$10:$F$2008,4,FALSE

 

[Guest]

In your if statement you only have the true responce It should answer false
when there is no error.
Tr
=IF(ISERROR(VLOOKUP(F12,CSSP!A10:F2008,4,FALSE)),"0.00",VLOOKUP(F12,CSSP!A10:F2008,4,FALSE))

 

[Dave O]

You're very close: you just need to repeat the VLOOKUP for the "TRUE"
argument of the IF. The first VLOOKUP is used with the ISERROR as the
logical test. The formula should read "If there is no error, then run
the VLOOKUP and return the result". Try modifying your formula to
=IF(ISERROR(VLOOKUP(F12,CSSP!A10:F2008,4,FALSE)),"0.00",VLOOKUP(F12,CSSP!A10:F2008,4,FALSE))

 

[Karl Schweitzer]

Another option is to use a user defined function that will return a
default value. The following is some VBA code that I use to avoid this
issue:


Public Function VLOOKUP_With_Default(Lookup_Value As Variant, _
Lookup_Table As Variant, Column_Offset As Variant, _
Optional Range_Lookup As Boolean = False, _
Optional Default As Variant) As Variant

' This User Defined Function runs the VLOOKUP worksheet function,
' but if there is an error in returning the result, it returns
' a default value. The default default value is a blank string.

Dim Result As Variant

On Error Resume Next
If TypeName(Default) = "Error" Then Default = ""
Result = Default
Result = Application.WorksheetFunction.VLookup(Lookup_Value, _
Lookup_Table, Column_Offset, Range_Lookup)

VLOOKUP_With_Default = Result

End Function



If you add this to the workbook you are working on (Alt-F11, menu
option Insert, Module, then copy and paste the text above into the code
pane that will be created, and menu option File, Close...), you can
change your formula to be something like:

=VLOOKUP_With_Default(F12,CSSP!A10:F2000,4,FALSE,0)

Where 0 will be returned if the value is not found (could be any value,
and could be a reference to a cell). The only downside of this
approach is that user defined functions are recalculated much more
frequently than other Excel functions--for large worksheets with many
occurrences of the function this can slow down recalculation.

 

[Guest]

Thank you that work for me perfectly

The problem is you didn't finish your IF statement. You told it what you
wanted to do if there was an error, but not what to do if there isn't an
error.

=IF(ISERROR(VLOOKUP(F12,CSSP!A10:F2008,4,FALSE)),"0.00",VLOOKUP(F12,CSSP!A10:F2008,4,FALSE))

Also, you might want to change your vlookup to
VLOOKUP(F12,CSSP!$A$10:$F$2008,4,FALSE