VLOOKUP AND #N/A

[Mike Saffer]

Greetings everybody,

I tried 3 other answers given in the archives first but still haven't been
able to sucessfully make this one work with VLOOKUP.

My formula in B6:
=IF(ISERROR(VLOOKUP(A6,H7:M121,3)),"",VLOOKUP(A6,H7:M121,3,FALSE))

I'm looking for for an exact match to A6 in column H of my VLOOKUP table
H7:M121 and return the value in the 3rd column, in this case column J.

Here's the kicker. I deliberately excluded what I was looking for in A6,
from cloumn H, trying to force an error of #N/A or #VALUE. Well, in this
case I got the #N/A error. error. I also tried =if(ISERROR(....... but no
joy. The error is still visable.

I was hoping to get a blank in B6 when there is an error, instead of a zero,
or #N/A, or #VALUE.

As always I appreciate any help.
Thanks,

Mike
Jacksonville, Florida

 

[Lars-Åke Aspelin]

Greetings everybody,

I tried 3 other answers given in the archives first but still haven't been
able to sucessfully make this one work with VLOOKUP.

My formula in B6:
=IF(ISERROR(VLOOKUP(A6,H7:M121,3)),"",VLOOKUP(A6,H7:M121,3,FALSE))

I'm looking for for an exact match to A6 in column H of my VLOOKUP table
H7:M121 and return the value in the 3rd column, in this case column J.

Here's the kicker. I deliberately excluded what I was looking for in A6,
from cloumn H, trying to force an error of #N/A or #VALUE. Well, in this
case I got the #N/A error. error. I also tried =if(ISERROR(....... but no
joy. The error is still visable.

I was hoping to get a blank in B6 when there is an error, instead of a zero,
or #N/A, or #VALUE.

As always I appreciate any help.
Thanks,

Mike
Jacksonville, Florida

I think you should have the fourth parameter (FALSE) also in the first
of the two VLOOKUP.

=IF(ISERROR(VLOOKUP(A6,H7:M121,3,FALSE)),"",VLOOKUP(A6,H7:M121,3,FALSE))

Hope this helps / Lars-Åke

 

[RagDyer]

Try this:

=IF(ISNA(MATCH(A6,H7:H121,0)),"",VLOOKUP(A6,H7:M121,3,0))

 

[Dave]

Hi Mike,
I think the problem is that you are missing the FALSE from the first
VLOOKUP. Without it, the error trap fails, then the IF function goes to the
second VLOOKUP, which does have a FALSE, and so produces the error. Try:
=IF(ISERROR(VLOOKUP(A6,H7:M121,3,FALSE)),"",VLOOKUP(A6,H7:M121,3,FALSE))

Regards - Dave.

 

[T. Valko]

Use the FALSE (or 0) argument in the ISERROR(VLOOKUP as well.

=IF(ISERROR(VLOOKUP(A6,H7:M121,3,0)),"",VLOOKUP(A6,H7:M121,3,0))

 

[ShaneDevenshire]

Hi Mike,

First I would write the formula as:

=IF(ISNA(VLOOKUP(A6,H7:M121,3,FALSE)),"",VLOOKUP(A6,H7:M121,3,FALSE))

or

=IF(ISNA(VLOOKUP(A6,H7:M121,3,0)),"",VLOOKUP(A6,H7:M121,3,0))

FALSE has been replaced by its equivalent 0, or

=IF(ISNA(VLOOKUP(A6,H7:M121,3,)),"",VLOOKUP(A6,H7:M121,3,))

in the last one FALSE and 0 are excluded but the comma after 3 is retained.

 

[Mike Saffer]

Thank you Dave and Lars Ake,

You both had the same answer and it works great now.

Could I please ask a follow up question?

Now that I am hiding the #N/A errors I still need to total the cells in a
coulmn that are numbers. I tried a simple sum formula in a column with 4
cells. 2 cells had a number an two cells had a hidden #N/A error. When I
try to sum I get anohter #N/A.

Here is an example:

A
1 1020
2 hidden #N/A
3 2240
4 hidden #N/A
5 #N/A

The formula, =sum(A1:A4) is in cell A5. The result I was looking for is, of
course, 3260. Do you have any ideas how to count the numbers and ignore the
errors?

Many many thanks,

Mike
Jacksonville

 

[T. Valko]

Assuming the numbers are positive:

=SUMIF(A1:A10,">0")

Or, this more generic version:

=SUMIF(A1:A10,"<>#N/A")

 

[Dave]

Hi Mike,
An easy way around this is to change the formula slightly. Instead of the
error trap inserting a "", use 0 (zero) instead. It will keep your SUM
function happy.

=IF(ISERROR(VLOOKUP(A6,H7:M121,3,FALSE)),0,VLOOKUP(A6,H7:M121,3,FALSE))

Regards - Dave.

 

[Mike Saffer]

Dear T. Valco, RagDyer & Shane,

Let me extend my thanks for your help. T. Valco, your formula about not
counting #N/A makes perfect sense to me now.

Glad you folks are out there!

 

[T. Valko]

You're welcome. Thanks for the feedback!