Trying VLOOKUP ActiveX Combolist value

[ike_barrett]

So I have database that has an employee table/sheet "Employees" with three columns...EmployeeID, FirstName, LastName. Most of the data only uses EmployeeID...but I don't want users to have to remember employee IDs when using it.

So, I use an ActiveX ComboBox to display the three columns and transmt the value to the linkedCell (in this case C3). The value shows correctly.

Then I try to show the names in D3 and E3, but keep getting #NA .

I used =INDEX(Employees,MATCH(C3,Employees[Employee_ID],0),2)

If I simply replace C3 with my employeeID number it works...also, if I change my employeeIDs to letters instead of numbers it works...but I can't do that.

I also tried substituting C3 with Indirect("C3") and that doesn't work (although it does work else where to display the EmployeeID).

Any direction you can provide would be great.

 

[Yongle]

Your formula works fine for me provided

EITHER
BOTH values are text

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]12345[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]joe[/td][td] =INDEX(Employees,MATCH(C3,Employees[Employee_ID],0),2)[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet8[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]Employee_ID[/td][td=bgcolor:#4472C4]First Name[/td][td=bgcolor:#4472C4]Last Name[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]12345[/td][td=bgcolor:#D9E1F2]joe[/td][td=bgcolor:#D9E1F2]Brown[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

23456​

[/td][td]peter[/td][td]Smith[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#D9E1F2]

34567​

[/td][td=bgcolor:#D9E1F2]jenny[/td][td=bgcolor:#D9E1F2]Silvers[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Employees[/td][/tr][/table]

OR
BOTH values are numbers

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

C​

[/th][th]

D​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

12345​

[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td]joe[/td][td] =INDEX(Employees,MATCH(C3,Employees[Employee_ID],0),2)[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Sheet8[/td][/tr][/table]

Excel 2016 (Windows) 32 bit
[Table="width:, class:head"][tr=bgcolor:#E0E0F0][th] [/th][th]

A​

[/th][th]

B​

[/th][th]

C​

[/th][/tr]
[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

1​

[/td][td=bgcolor:#4472C4]Employee_ID[/td][td=bgcolor:#4472C4]First Name[/td][td=bgcolor:#4472C4]Last Name[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

2​

[/td][td=bgcolor:#D9E1F2]

12345​

[/td][td=bgcolor:#D9E1F2]joe[/td][td=bgcolor:#D9E1F2]Brown[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

3​

[/td][td]

23456​

[/td][td]peter[/td][td]Smith[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#E0E0F0]

4​

[/td][td=bgcolor:#D9E1F2]

34567​

[/td][td=bgcolor:#D9E1F2]jenny[/td][td=bgcolor:#D9E1F2]Silvers[/td][/tr]
[/table][Table="width:, class:grid"][tr][td]Sheet: Employees[/td][/tr][/table]


Where is the mismatch?
You say that "If I simply replace C3 with my employeeID number it works"
- so I think that the problem is in C3 rather than the table

Try this simple formula
=T(C3)

It returns nothing if the cell contains a number and returns 12345 (in above example) if cell contains text

If I am correct then your formual can be changed to
=INDEX(Employees,MATCH(VALUE(C3),Employees[Employee_ID],0),2)