My VLOOKUP is showing a lot of #N/A entries when no match. Cant it just return blank?

cruttley

New member
Joined
Mar 19, 2014
Messages
3
Reaction score
0
Points
0
I have a VLOOKUP that works fine when there is match, but when there isnt it shows a value of #N/A in the cell. Is there any way to not show the ugly #N/A value?
My lookup table is sorted, and I am matching on email address.

See screen shot attached.

Also when there is a match, if the lookup cell has a blank in it, for some reason it is returning a 0.

2014-03-19_17-34-56.jpg
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Try

=IFERROR(IF(VLOOKUP($A5,Results!$C$1:$)($50,3,False))=0,"",VLOOKUP($A5,Results!$C$1:$)($50,3,False)),"")
 
Last edited:

cruttley

New member
Joined
Mar 19, 2014
Messages
3
Reaction score
0
Points
0
Thanks Bob. I will try that out tomorrow!
Much appreciated.
 

cruttley

New member
Joined
Mar 19, 2014
Messages
3
Reaction score
0
Points
0
Thanks Bob. I will try that out tomorrow!
Much appreciated.

I had to tweak the formula a bit to get it to work....but all is well now and now I understand the IFERROR function.

=IFERROR(IF(VLOOKUP($A5,Results!$C$1:$O$50,3,FALSE)=0,"",VLOOKUP($A5,Results!$C$1:$O$50,3,FALSE)),"")
 

Bob Phillips

Super Moderator
Staff member
Joined
Mar 21, 2011
Messages
1,942
Reaction score
0
Points
36
Excel Version(s)
O365
Goodness, I don't know what happened there (let's just pretend I did it deliberately to get you to think about it and understand it - you passed with flying colours :)). Glad you sussed it.
 
Top