VLOOKUP AND #N/A
 

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,H 7: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

VLOOKUP AND #N/A
 

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

VLOOKUP AND #N/A
 

Try this:

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


--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Mike Saffer" wrote in message
...
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,H 7: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

VLOOKUP AND #N/A
 

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

Regards - Dave.

VLOOKUP AND #N/A
 

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))


--
Biff
Microsoft Excel MVP


"Mike Saffer" wrote in message
...
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,H 7: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

VLOOKUP AND #N/A
 

Hi Mike,

First I would write the formula as:

=IF(ISNA(VLOOKUP(A6,H7:M121,3,FALSE)),"",VLOOKUP(A 6,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.
--

Cheers,
Shane Devenshire


"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,H 7: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

VLOOKUP AND #N/A
 

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

VLOOKUP AND #N/A
 

Assuming the numbers are positive:

=SUMIF(A1:A10,"0")

Or, this more generic version:

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

--
Biff
Microsoft Excel MVP


"Mike Saffer" wrote in message
...
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

VLOOKUP AND #N/A
 

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.

VLOOKUP AND #N/A
 

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!


--
Mike
Jacksonville, Florida


"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"Mike Saffer" wrote in message
...
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,H 7: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

VLOOKUP AND #N/A
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mike Saffer" wrote in message
...
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!


--
Mike
Jacksonville, Florida


"T. Valko" wrote:

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))


--
Biff
Microsoft Excel MVP


"Mike Saffer" wrote in message
...
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,H 7: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