Error trapping for VLOOKUP...doesn't work so help!

  • Thread starter Thread starter Toby Erkson
  • Start date Start date
T

Toby Erkson

I want to trap any error that may occur using a VLOOKUP.
I tried this in a cell:
=if(iserror(VLOOKUP(K1363,LOOK,6,FALSE)),"No lookup value error", VLOOKUP(K1363,LOOK,6,FALSE))
but this won't work because the value_if_false part of the IF() statement corrupts (for lack of a know word) the IF() statement. That is, if there actually is
an error with the VLOOKUP, the ISERROR will evaluate to TRUE but the VLOOKUP will also dump its error into the IF() statement and that error keeps the IF()
statement from operating.

Suggestions on trapping VLOOKUP errors and "fixing" them like I'm trying to do with the IF() statement? The IF() statement isn't necessary, it's just what I
though would logically work...
Toby Erkson
Oregon, USA
 
Hi Toby
your formula should work. What error did you receive?

--
Regards
Frank Kabel
Frankfurt, Germany

I want to trap any error that may occur using a VLOOKUP.
I tried this in a cell:
=if(iserror(VLOOKUP(K1363,LOOK,6,FALSE)),"No lookup value error", VLOOKUP(K1363,LOOK,6,FALSE))
but this won't work because the value_if_false part of the IF()
Click to expand...
statement corrupts (for lack of a know word) the IF() statement. That
is, if there actually is
an error with the VLOOKUP, the ISERROR will evaluate to TRUE but the
Click to expand...
VLOOKUP will also dump its error into the IF() statement and that error
keeps the IF()
statement from operating.

Suggestions on trapping VLOOKUP errors and "fixing" them like I'm
Click to expand...
trying to do with the IF() statement? The IF() statement isn't
necessary, it's just what I
 
The formula just stays visible in the cell, no error or result are displayed. When I click the Insert Function button and look at the Function Arguments dialog
I see the =#N/A error to the right of the "Value_if_false" argument box. The "Logical_test" and "Value_if_true" boxes show the correct values (in my case,
"True" and "No lookup value error".

Toby

Hi Toby
your formula should work. What error did you receive?
Click to expand...

Toby Erkson
Oregon, USA
 
Hi
your cell is probably formated as 'Text'. Goto 'format - Cells' and
format the cell as 'General'. After this re-enter your formula

--
Regards
Frank Kabel
Frankfurt, Germany

The formula just stays visible in the cell, no error or result are
Click to expand...
displayed. When I click the Insert Function button and look at the
Function Arguments dialog
I see the =#N/A error to the right of the "Value_if_false" argument
Click to expand...
box. The "Logical_test" and "Value_if_true" boxes show the correct
values (in my case,
"True" and "No lookup value error".

Toby
Click to expand...
 
Frank, my man, you are awesome! That was my problem! I doubt that a cell's format would have ever crossed my mind! I don't see the logic in it...maybe I need
another swig of coffee :-)

Toby

Hi
your cell is probably formated as 'Text'. Goto 'format - Cells' and
format the cell as 'General'. After this re-enter your formula
Click to expand...

Toby Erkson
Oregon, USA
 
Back
Top