vlookup to return blanks for errors & blank cells

[ariesmelissa]

I am trying to do a vlookup to return blanks for errors & blank cells. I put together these two formulas but I can't get them to work together... I know it's like a simple punctuation that I'm missing because both of these formulas work but I'm sort of pounding my head on the wall here.

=IF(ISERROR(VLOOKUP($C:$C,'CASPR - NSB'!$A:$BS,71,FALSE)),"",(VLOOKUP($C:$C,'CASPR - NSB'!$A:$BS,71,FALSE))),IF(ISBLANK(VLOOKUP($C:$C,' CASPR - NSB'!$A:$BS,71,FALSE)),"",(VLOOKUP($C:$C,'CASPR - NSB'!$A:$BS,71,FALSE)))

Thank You!

[JBeaucaire]

Hi Melissa. Welcome to the forum. Be sure to read through the Forum Rules so you can use and follow them effectively. For instance, you'll need to edit that post above and update the title to one that summarizes the problem.

After typing out your question, a title often jumps off the page at you. For instance, from your first sentence: vlookup to return blanks for errors & blank cells

EDIT your original post, click Go Advanced and update the title.

I have a couple ideas, I'll think about them while you update the thread topic.

[ariesmelissa]

Thanks. Let me know what you come up with.

[JBeaucaire]

You're using Excel 2007 which has a neat little function called IFERROR(). You can wrap any existing formula into an IFERROR() trap to tell Excel what to do if the formula inside results in an error. Usage goes something like this:

=OriginalFormula

...becomes

=IFERROR(OriginalFormula, "")


Press F1 in Excel and read up further on other examples.

[ariesmelissa]

Hi,
This is what I ended up using:

=IF(OR(ISERROR(VLOOKUP($C:$C,'CASPR - RELO'!$A:BL,64,FALSE)),ISBLANK(VLOOKUP($C:$C,'CASPR - RELO'!$A:BL,64,FALSE))),"",VLOOKUP($C:$C,'CASPR - RELO'!$A:BL,64,FALSE))


Thanks for your help!