Displaying blank field instead of #N/A

[Guest]

I have created a form to be completed requesting a server be moved from one
location to another. The mover needs to see the server name and the server
type. Starting in cell B77 (and going down the column) I have listed all the
servers. Starting in cell C77 I have listed the type of server named in cell
B77.

In my form, I used the Data Validation function, I have created a drop down
menu of servers to choose from in cell B21. In cell C21 I am using the
formula =VLOOKUP(B21,B77:C529,2). This formula looks in cell B21 and if it
sees the name of a server from the list in cells B77:C529 it them displays
what is in the 2nd column (the server type).

The problem is, if there is nothing listed in cell B21, then #N/A is
displayed in cell C21. How can I have cell C21 be blank, or not show
anything until an entry is placed in cell B21? Thanks for your help!

 

[Biff]

Hi!

Try this:

=IF(B21="","",VLOOKUP(B21,B77:C529,2))

Biff

 

[Guest]

Thanks for the help Biff. I tried your suggestion, but it did not work, #N/A
is still displayed instead of a blank cell.

 

[Peo Sjoblom]

The only way that would be possible would be if the error comes from not
finding the value from B21
in the lookup table also note that you are not looking for an exact match,
try

=IF(ISNUMBER(MATCH(B21,B77:B529,0)),VLOOKUP(B21,B77:C529,2,0),"")

 

[Guest]

Thanks Peo!! Your suggestion worked perfectly!

The only way that would be possible would be if the error comes from not
finding the value from B21
in the lookup table also note that you are not looking for an exact match,
try

=IF(ISNUMBER(MATCH(B21,B77:B529,0)),VLOOKUP(B21,B77:C529,2,0),"")