Vlookup based on Drop down fails

[Phil Smith]

I have a cell with a dropdown based on a list q3:q45
I have a cell with Vlookup, data to lookup from that cell, range q3:q45

As you can see, it will be the same data, so there WILL be a match at
all times. Yes, the data range is sorted alphabetically.
If I change my lookup cell to a normal cell, cut and paste into it from
my list, the formula works.

What gives? Any Ideas?

 

[Phil Smith]

Something else I noticed: My dropdown is a list of NAMES, which are
TEXT. However, if I reference that Dropdown from another cell, just
=e1, where e1 is the drop down, I get the number zero, not the text
displayed in the drop down.

 

[T. Valko]

if I reference that Dropdown from another cell,

just =e1, where e1 is the drop down, I get the
number zero, not the text displayed in the drop down.

Is your drop down list in merged cells? If so, then the drop down is
actually residing in the top left cell of any merged cells.

 

[T. Valko]

Another thought...

Are you using a Data Validation drop down list or are you using a Combo Box
list?

 

[Phil Smith]

Not Merged cells. I am pretty sure that it is a Data Validation drop
down list, but I am not sure. I did not create this spreadsheat, I am
being asked to build a couple of gnarly formulas based on these boxes,
and I have not worked with data validation boxes too much.

One thing I noticed is that I can grab and drag this thing and drop it
between cells, etc. So it does notr appear to be a cell at all. It has
a name, Drop DOWN 14. Would I referece it by that? IF so, how?

 

[T. Valko]

Ok, that sounds like a combo box. A combo box doesn't reside in a cell, it
"floats" on top of the sheet. When you make a selection from a combo box the
selection doesn't autoamtically get entered in a cell. You have to set the
combo box to have a linked cell. This linked cell will return the *index
number* of the selection.

Saved from an old post:
Combo box vs data validation drop down list differences

It depends on what type of combo box you're using. The main difference
between a combo box and a data validation drop down list is that a combo box
does not occupy a cell. A combo box "floats" above the worksheet. A data
validation drop down list does occupy a cell. So, when you make a selection
from a data validation drop down list that selection is entered as the value
of the cell. When you make a selection from a combo box (there are 2 types
of combo boxes) that selected value is not entered into any cell
automatically. You have to "format" the combo box to enter the selection in
a cell and the cell can be any cell of your choice. The cell that will hold
the selection is called the linked cell. In other words, this cell is linked
to this combo box.

Now comes the fun part!

As I said, there are 2 types of combo boxes, a Forms Toolbar combo box and a
Control Toolbox combo box. Each of these handles the linked cell a different
way. A Forms combo box will return the index number of the selection to the
linked cell. A Control combo box will return the actual selection to the
linked cell. For example:

Tom
Bill
Sue
Lisa

Let's say you select Sue from the combo box. If it's a Forms cb, then the
linked cell will return the index number 3 because Sue is the 3rd item in
the cb. A Control cb will return the actual selection Sue to the linked
cell.

So, if you need a formula that uses the selection from the cb you have to
reference the linked cell. If it's a Control cb then the reference is pretty
straightforward. If it's a Forms cb then you have to "translate" the
selection index number to the actual selection. In other words, you have to
somehow make 3 = Sue.

The easiest way to do this is to use an INDEX function. You would index the
source of the cb and use the linked cell to tell INDEX which value you want.

Suppose the list of names above is in the range A1:A4 and has the defined
name of Names. The source of the cb would be Names. Let's assume the linked
cell is B1. To do your lookup:

=VLOOKUP(A2,sheet2!A1:E22,COLUMNS(A:B),0)

If it's Control cb:

=VLOOKUP(B1,sheet2!A1:E22,COLUMNS(A:B),0)

If it's Forms cb:

=VLOOKUP(INDEX(Names,B1),sheet2!A1:E22,COLUMNS(A:B),0)

Here's another "neat" thing about combo boxes. Since they "float" above the
worksheet you can "hide" stuff under them. This is the perfect location for
the lnked cell.
Sounds pretty confusing, doesn't it? It's actually fairly simple once you
get the hang of it.

 

[Phil Smith]

It is a Forms Control box, which was not linked to any cell. This was
originally designed just for display purposes, with nothing intended to
be driven from it.

I know know how to deal with it, and it works perfectly.

Thank you very much!
Phil

 

[T. Valko]

Good deal. Thanks for the feedback!

 

[Phil Smith]

I am running into an issue with this box. Any insight?
It is the Forms Control style, which means it is giving me an index#.

The problem, choosing nothing gives me a 1, choosing the first entry
gives me a 2, etc. easy enough to compensate for, but is that correct?
he range is set to my data correctly, which has no headers.

 

[T. Valko]

The problem, choosing nothing gives me a 1,

choosing the first entry gives me a 2, etc.
easy enough to compensate for, but is that correct?

No.

When you first setup the combo box it is empty until you make an initial
selection. Once you make an initial selection it should always return the
index number of whatever item is selected. So, in essence, after the initial
selection some item is *always* selected unless you clear the linked cell.