VLOOKUP AND #N/A

M

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
 
L

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
 
D

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

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

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.
 
M

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

T. Valko

Assuming the numbers are positive:

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

Or, this more generic version:

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

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.
 
M

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!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How to correct a #N/A in vlookup 1
Help With VLOOKUP 2
Same forumula returns both 0 & N/A 2
vlookup returns na 7
ISERROR on VLOOKUP 3
VLookup + IF 5
VLOOKUP return #N/A 4
Average of Vlookup data 3

Top