SOLVED

vlookup and drop down list shows #N/A

Copper Contributor

So I have been following many different tutorials on using a drop down list and VLOOKUP to return additional data for the drop down item selected.
I set up a list of names in the range of C2:C6 for the drop down list (Cell A2 is where you will select the item from the list) I selected A2, then Data Validation, set to List and the range was selected)
I have a second column D2:D6 with the associated persons age.
I wish to have cell B2 display the age associated with the person selected from the drop down in A2.
so in cell B2 I put the following formula: =VLOOKUP(A2,B2:C6,2,False) This works as requested.
The issue is that before any selection is made from the drop down, the age cell (B2) displays #N/A
I believe this happens because there is nothing in A2 yet. I have searched endlessly for a solution, but only get the standard results to check my formula for syntax or check that numbers are numbers and not text, look for beginning or trailing spaces, etc.
How can I get it to stop displaying #N/A before choosing the item in the drop-down.
Thanks

2 Replies
best response confirmed by Hans Vogelaar (MVP)
Solution

@AloysiousB 

=IFERROR(VLOOKUP(A2,B2:C6,2,False),"")
OR
=IFNA(VLOOKUP(A2,B2:C6,2,False),"")

@dscheikey 

The IFERROR line worked perfectly.

 

Thanks so much

1 best response

Accepted Solutions
best response confirmed by Hans Vogelaar (MVP)
Solution

@AloysiousB 

=IFERROR(VLOOKUP(A2,B2:C6,2,False),"")
OR
=IFNA(VLOOKUP(A2,B2:C6,2,False),"")

View solution in original post