Tell me more ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.
up vote 0 down vote favorite

I'm using the VLOOKUP function and I have this function =VLOOKUP(B9,Products!A5:K29,11,FALSE)

It works but what do I need to add so the cell will display nothing if B9 is nothing? At the moment if B9 is nothing it displays "#N/A". Thank you.

share|improve this question

migrated from stackoverflow.com Aug 1 '12 at 2:59

2 Answers

up vote 1 down vote accepted

You can surround the whole thing with an if statement thus:

=IF(B9="","",VLOOKUP(B9,Products!A5:K29,11,FALSE))

with any suitable changes to the B9="" condition.

That way it gives you "" if B9 is "" otherwise it gives you the result of the lookup.

share|improve this answer
Oh thank you, I didnt realize that you can use other functions within a function. – jay Oct 18 '11 at 2:22
@jay: yes, in my early days (before I knew about things like lookups), you used to find monstrosities in my spreadsheets like =if(b1="", if(b2="", if(b3="", if(b4="", if(b5="", "?", b5), b4), b3), b2), b1) :-) – paxdiablo Oct 18 '11 at 2:25
Haha I see. Hey man I applied this formula to every cell in the column but how do I make A5:K29 default? It changes as you down the column. – jay Oct 18 '11 at 2:29
@jay, you need to make the range non-relative, something like: $A$5:$K$29. That will lock the values no matter where you move or copy the formula to (you can also independently set row and column non-relative but you probably don't need that in this case). – paxdiablo Oct 18 '11 at 2:30
Jay, use absolute references, ie =IF(B9="","",VLOOKUP(B9,Products!$A$5:$K$29,11,FALSE)) [dang, damn this doubling up] – brettdj Oct 18 '11 at 2:31
show 2 more comments
up vote 1 down vote

For your question as asked

=IF(B9="","",VLOOKUP(B9,Products!A5:K29,11,FALSE))

Going a step further in case your value in B9 does not exist in your lookup range =IF(B9="","",IF(ISNA(VLOOKUP(B9,Products!A5:K29,11,FALSE)),"Not found",VLOOKUP(B9,Products!A5:K29,11,FALSE)))

Useful reference. Patrick Matthews (MVP Excel), Six Reasons Why Your VLOOKUP or HLOOKUP Formula Does Not Work

[The other post wasn't flagged to me at the time when I posted, that said I do recommend the additional ISNA test that I added]

share|improve this answer

Your Answer

 
discard

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