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


--
Mike
Jacksonville, Florida


"Lars-Åke Aspelin" wrote:

On Mon, 9 Jun 2008 12:26:03 -0700, Mike Saffer
wrote:

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)),"",VLOOKU P(A6,H7:M121,3,FALSE))

Hope this helps / Lars-Åke