# Excel VLOOKUP Function

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.

-

## migrated from stackoverflow.comAug 1 '12 at 2:59

This question came from our site for professional and enthusiast programmers.

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.

-
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

`=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)))`