How to vlookup to return blank instead of 0 or N/A in Excel?
Normally, when you apply the vlookup function to return the corresponding value, if your matching cell is blank, it will return 0, and if your matching value is not found, you will get an error #N/A value. Instead of displaying the 0 or #N/A value, how can you make it show blank cell?
Vlookup to return exact matching value from a list table
With this formula of Kutools for Excel, you can quickly vlookup the exact value from a list without any formulas.
Kutools for Excel: with more than 200 handy Excel add-ins, free to try with no limitation in 60 days. Download and free trial Now!
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
The following formulas may help you to solve this problem:
Vlookup to return blank instead of 0
Please enter this formula: =IF(LEN(VLOOKUP(E1,A2:B10,2,0))=0,"",VLOOKUP(E1,A2:B10,2,0)) into a blank cell you need, and then press Enter key, you will get a blank cell instead of the 0, see screenshot:
Note: In the above formula, E1 is the criterion which you want to return its relative value, A2:B10 s the data range you use, the number 2 indicates which column that the matched value is returned.
Vlookup to return blank instead of #N/A error value
To replace the #N/A error with blank cell if your searched value is not found, you can use this formula: =IFERROR(VLOOKUP(E1,A2:B10,2,FALSE),""), enter it into a blank cell, and press Enter key to get the result you need, see screenshot:
Note: In this formula, E1 is the criterion which you want to return its relative value, A2:B10 s the data range you use, the number 2 indicates which column that the matched value is returned.
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 1 months agoI think this is a good solution but is there a way around the problem that if I use it and then COUNTA() on the cells where the formula is created it counts those cells with "".
To post as a guest, your comment is unpublished.· 6 months agoI am not a tech guru - I just want to add to my formula that if it does not find anything in the cell - it gives an accounting 0 that will be acknowledged in my total formula. =VLOOKUP(F4,Sponsorships1,2,FALSE)
To post as a guest, your comment is unpublished.· 10 months agofor blank values the LEN function is converting true zero's from the lookup data to blank. Any ideas on how to bring true zeros across and only convert the blanks to blank?
To post as a guest, your comment is unpublished.· 1 years agoThank you, it worked!
To post as a guest, your comment is unpublished.· 1 years agoThanks sky yang - This is awesome!
I applied the formula you provided and I changed the cell references but now I'm getting #N/A instead of 0 or a negative symbol.