vlookup function returning #n/a

  • Thread starter Thread starter brian
  • Start date Start date
B

brian

This is been difficult. If I reference a value within
A10 that is not located in the table, #N/A is returned.
How can I make it so that the cell remains blank rather
than #N/A.

=VLOOKUP(A10,'Daily Patrol Log'!$Q$22:$W$40,6,false)

Thanks,
Brian
 
Hi Brian,

=if(isna(=VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,false),"",=VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,false))

Regards,
Kevin
 
one way:

=IF(ISNA(MATCH(A10,'Daily Patrol Log'!$Q$22:$W$40,FALSE)),"",
VLOOKUP(A10,'Daily Patrol Log'!$Q$22:$W$40,6,FALSE))
 
One way


=IF(COUNTIF('Daily Patrol Log'!$Q$22:$Q$40,A10),VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,false),"")
 
Hi, thank you for your response. I pasted your formula in
and it was rejected right after the second open
parenthesis.
Right here>
=if(isna( =VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,false),"",=VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,false))
Click to expand...
What do you think the problem is.

I'm sorry and I really do appreciate your help I have to
have this into my supervisor tonight.
thanks,
brian
Thanks again
 
Brian,

Whoops, if you look at my prior answer, I had "equals" signs buried inside.

It should work now.

Regards,
kevin

=if(isna(VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,false),"",VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,false))
 
Thanks again, I think it's a little bit closer. However,
now the formula gets rejected at the "" marks in the
middle of the string. I hate to linger on this one
problem, but I'm so close to being finished.

Thanks,
Brian

=if(isna(VLOOKUP(A10,'Daily Patrol
 
Brian,

My humblest apologies.

Try this:

=IF(ISNA(VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,FALSE)),"",VLOOKUP(A10,'Daily Patrol
Log'!$Q$22:$W$40,6,FALSE))

The difference is that I add a ")" after the first false to close off the
ISNA loop.

You should be on your way now!

Best regards,
Kevin
 
Oh boy, I'll bother just one more time before I give up on
this problem.
I tried the newest way and just as I entered the formula,
I was sent to the My Documents box where it indicated file
not found for Daily Patrollog. I then hit the cancel
button and the formula was entered. The cell no longer has
the #N/A and is blank now no matter if the number
referenced is or is not in the vlookup table.
 
Disregard that last post,
there was no space between the sheet referenced (Daily
Patrollog) it should have read (Daily Patrol Log) I
corrected it and your formula worked great.
The knowledge that you guys have about this stuff amazes
me.

Thank You,

Brian
 
Brian,

Excel is attempting to find your file

"Daily Patrol Log"

If this file is open, then it should just use the file. If the file is
closed, the Excel will prompt you for the file. If the file is misspelled,
you must correct the spelling.

Can you send me your files (this current file, plus Daily Patrol Log). If
you need to, delete the sensitive data and replace dummy data.

This should be quick, so if you can send your file to my last name (see
message) at ya and hoo (one word) and you know the rest.

Hope this helps.

Regards,
Kevin
 
Brian,

Whew!!

Glad it worked. I hope you understand the formula after all that effort.

In essence, it says,

=if(ISNA(does the formula result in #NA?), Yes-then display "", No-Calculate
as per normal).

Now that you understand this formula, you might want to examine the other
solutions too. As has been shown, there is more than one solution. They
tend to be variations on a theme. Which one you choose is a matter of
personal preference.

Regards,
Kevin
 
I think you wanted a single column in the match portion (copy|paste is sometimes
a problem!):

=IF(ISNA(MATCH(A10,'Daily Patrol Log'!$Q$22:$Q$40,FALSE)),"",
VLOOKUP(A10,'Daily Patrol Log'!$Q$22:$W$40,6,FALSE))
 
i 've try both method (the countif one and the match
one), but it then shows none result, even though the ref
no. i entered should have its corresponding value in the
parents data file. so, wht's the problem ?!
can u all give a brief explaination for the structure of
the formula?

pls help , thank you.
 
The =countif() version looks at Q22:Q40 and counts how many times A10 appears in
that range. If it finds any, then it uses the =Vlookup() function. If it
doesn't find any, then it plops in "" (so it looks empty).

The =match() version will return a number indicating what row of Q22:Q40 has the
same value as A1. If it doesn't find one, then that =match() returns #n/a.

J.E.'s formula: If it doesn't find a match (#n/a would be returned), then show
"", else show the =vlookup result.

Common problems in =vlookup()s are the data doesn't quite match--extra leading
spaces/extra trailing spaces or even extra internal spaces.

And if your data is numeric, then it has to be the same in both spots. If your
value is '123 (leading apostrophe-or cell formatted as text) and you try to
match it against 123 (really numeric), then the match won't be found.

(But I don't think that's the problem with your formula. =Countif() is more
forgiving (but =match() and =vlookup() isn't). You'd get a different error
(#n/a from =vlookup().))

And if you obtained your data from a web site, those things that look like
spaces may not be spaces. They could be char(160)'s (non-breaking spaces in
html speak.)

Try retyping the data that should match (both spots) and see if that fixes it.

If it does, you may want to use David McRitchie's TrimAll subroutine:
http://www.mvps.org/dmcritchie/excel/join.htm#trimall
(look for "Sub Trimall()")

David also has instructions on how to install a macro and run it at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Back
Top