Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

If VLOOKUP doesn't find a match, it returns something that looks like "#__". However, I want to manipulate the VLOOKUP formula not to write anything in the cell if it doesn't find a match. (This picture is from Turkish Excel.)

How can I get VLOOKUP to return an empty result if it doesn't find anything?

alt text

share|improve this question
    
" Manupilate "? –  Hello71 Aug 19 '10 at 15:28

2 Answers 2

up vote 4 down vote accepted

You simply need to add an IF / ISNA command to the Vlookup function

=IF(ISNA(VLOOKUP(YOUR LOOKUP,FALSE)),"VALUE HERE",VLOOKUP(YOUR LOOKUP,FALSE))

You need to make sure that the last parameter or the Vlookup is set to false, and where I wrote Value Here, you need to keep the quotes, but simply have a blank space if you want it to be blank.

If your Vlookup command was : =VLOOKUP(B7,testsheet,2) and you wanted the value to be blank if it doesn't exist, you would use:

=IF(ISNA(VLOOKUP(B7,testsheet,2,FALSE)),"",VLOOKUP(B7,testsheet,2,FALSE))

Hope this helps you!

share|improve this answer

If you are using Excel 2007 you can use the IFERROR function:

=IFERROR(VLOOKUP(LookupCell,LookupTable,Column,Type),"")

It's cleaner looking and more efficient than Wil's version but it is not backward compatible. If you share your xls w/ an Excel 2003 user the formula will error. In that case I would use Wil's method.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.