VLookup: Can formula use value loaded from a drop down list?

[Guest]

I created a Travel Expense form for my small company. I want values to be
filled in automatically once the person types in their Name on the first
worksheet. (Name is filled in via a drop down list in B2. All Reference data
is on additional sheets, but for this example, "Employee Name" and "Employee
#" is on the worksheet titled Employee.)

Formula used: =vLOOKUP("b2",Employee!a:b,2,FALSE)

"B2" is populated by the drop down and is the Employee Name. Employee
worksheet looks like this:

Employee Name Employee #
Joe Schmoe 001
Bob Smith 002

I have inserted this formula in B3, because that's where I want the Employee
# to populate. However, after inserting the formula, it gives me the dreaded
#n/a response. I've stepped through the formula and I see that it is reading
the name from the drop down properly, so what am I forgetting to check? I
also removed the quotes around the B2...but then I get the #REF!
error...which is worse, I think.

Could it be that my numbers in the employee number column are text? (I need
it to be because some have letters in their ID.) I did change it to see if
it made a difference, but it did not. I am stumped.

Thank you for your time.
J.

 

[T. Valko]

So you're looking up the employee number based on the name?

If the formula is returning #N/A that means it is not finding a match of the
name in the lookup table.

What type of drop down are you using? A data validation list or a combo box?
A data validation list populates the cell that contains the drop down list
with the selected value. A combo box doesn't actually reside in a cell and
the selection made also doesn't populate a cell with the selected value.

Formula used: =vLOOKUP("b2",Employee!a:b,2,FALSE)

If B2 is the cell with the drop down list then remove the quotes:

=VLOOKUP(B2,Employee!A:B,2,0)

That may be why you were getting the #N/A error.

I also removed the quotes around the B2...
but then I get the #REF! error...

Hmmm...

That's a tough one to try to figure out. Do you have any formulas in
Employee!A:B that are returning #REF! ?

=VLOOKUP(B2,Employee!A:B,2,0)

That formula is syntactically correct. #REF! means that a reference is
invalid but your formula is very simple and nothing sticks out as being an
invalid reference.

Could it be that my numbers in the employee
number column are text?

Nah, that's not the problem, but see my point above about #REF! errors in
the lookup table.

 

[Guest]

Thanks for the quick reply...
If you mean "filling the cel" by saying "looking up", then yes, that's my
final goal.

I have created a data validation list for the employee name. So it HAS to
be in the column and should not be returning #N/A. I'm stumped!

No, I do not have any formulas in Employee!A:B that are returning #ref!.
All of the fields are just text. (names and id #s...no formulas at all)

But you made me realize that I have some blank cells, so I'll remove those
and try again....brb....

Nope, I removed any row that had a blank cell in it and still got *N/A.
Definite sad face here.

j.

 

[Bob Phillips]

Not especially that the formula should reference B2, not "B2", as the latter
is a string value, not the DV cell.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Is the source of the DV drop-down the same as the Employee table?

You will get N/A if for example there re blanks at the end of the name in
one of the lists.

I created a named range "Employee_Name" based of your employee table (in
"Employee") and used this as the list in the DV. This ensures they will match.

The VLOOKUP worked fine.