I am doing a little format in which I call data from other cells, from other worksheet to fill it out. I am using VLOOKUP to do that.

So as an example I have:

Name: (Drop down list)

When I select a Name from the drop-down list, the fields for Sex and Organization are filled out automatically, because they have a VLOOKUP formula. The values are taken from a database in another sheet.

However, Items does not fill out. I get "0" as the value it returns. The reason for that, is that the value of items in the database is a drop-down list.


Jhon   Adams       UN     item 1  (This is a drop down list (Male/Female)

The formula I am using is: VLOOKUP(B2,A2:Y40,2,FALSE)

  • B2 - is the Name field (as a drop down list)... all of this in my little format
  • A2:40 - is the entire range of the database 2 - the column the field I want to excel to look up (In this case LAST NAME).

If I do the same formula for SEX, (so instead of VLOOKUP(....,2,FALSE), it would be VLOOKUP(......,4,FALSE) it returns a "0". I imagine that it is because that column is made up with drop-down lists to select Male or Female from a drop-down list.

How can I make it so that when I do VLOOKUP I get the sex displayed correctly? Or which formula can I use so that it does display.

I have another case with ITEMS, but instead of selecting just ONE (Male OR Female) you can select many items as you want from the drop-down list How can I do it so that it displays the data correctly, instead of displaying "0".


If Vlookup returns a zero that means that the cell it returns is empty. Whether or not the cell has a data validation drop down is unimportant. What matters is the value of the cell. If it has text, text will be returned. If it has a number, a number will be returned. If it is empty, a zero will be returned.

In fact, any formula that returns a cell value will show a 0 if the cell is empty.

  • Thank you for answering. I solved the isue with Male or Female, and I alrady made it work. I think I found out why it returns zero with items though. The cell that is a drop down list from the database (the one I want the value from), it's configured with a Macro to have multiple selections. For example: You can select Pencil, Pen, Ereaser, etc (as many as you want).
    – colonel179
    Jul 7 '15 at 15:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.