Home |
Search |
Today's Posts |
#1
|
|||
|
|||
VLOOKUP ERROR
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. |
#2
|
|||
|
|||
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 "Mark Adams" wrote: 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. |
#3
|
|||
|
|||
In your if statement you only have the true responce It should answer false
when there is no error. Try =IF(ISERROR(VLOOKUP(F12,CSSP!A10:F2008,4,FALSE))," 0.00",VLOOKUP(F12,CSSP!A10:F2008,4,FALSE)) "Mark Adams" wrote: 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. |
#4
|
|||
|
|||
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)) |
#5
|
|||
|
|||
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. Mark Adams wrote: 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. |
#6
|
|||
|
|||
Thank you that work for me perfectly
"Jonathan Cooper" wrote: 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 "Mark Adams" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup Error | Excel Worksheet Functions | |||
Vlookup & Lookup function error | Excel Worksheet Functions | |||
Vlookup Syntax Error | New Users to Excel | |||
Vlookup #N/A error due to formatting | Excel Worksheet Functions | |||
#N/A error with VLOOKUP | Excel Discussion (Misc queries) |