Excel/vlookup combo box
QUESTION: Hi tom!
I am trying to do a vlookup in the cell that is linked to my combo box, but the formulas I keep putting in are not working. The input range is the left side of my vlookup table. When you scroll down and choose one of the items in the combo box it puts the number that it is in line. For example, if I choose the Part # 635830-7 it returns a 6 in the cell link box because it is 6th in line. What I would like it to do is for the cell link box to read what is right side of the Part number and return that value. So when I choose Part # 635830-7 in my combo box, instead of 6 showing up 22.214.171.124. Enclosures shows up.
I attached an image to give you a better idea!
Thank you for all of your help!
Based on your description, it sounds like you are using a combobox from the forms controls. This type of combobox does return the position in the list for the item chosen. When I use this type of box, link it to the cell underneath the combobox so the 6 would appear say in cell c1.
then you could use your vlookup in E1 with a formula like
So the index using the 6 found in C1 (where I would link the combobox) to get the part number from column A. then I use that part number in the vlookup to return the 126.96.36.199 Enclosures from column B.
If you use the ActiveX Combobox, it will return the part number so you could avoid the use of Index.
---------- FOLLOW-UP ----------
That worked perfect! But I need the font size bigger so people can read the values clearly, and im aware that you can only change the font size if the combo box is an ActiveX control. I figured out how to get my range of data to display in the combo box, but I cant seem to get that same vlookup format to connect with the activeX combobox like i did with the form control combo box. How would I go about doing the same thing with the ActiveX combo box?
If you linked the output of the activeX combobox to cell C1, then in C1 you should see the part number (or if C1 is below the combobox, you won't see it, but it will be there.
So now you can just modify the formula in E1 to use the part number directly:
you would do
since C1 now contains the selected part number instead of the location of the part number (so the embedded index function is not needed as it was returning the part number)
---------- FOLLOW-UP ----------
It seemed to work! Exept for a few of the Part #'s come up as #NA in E1... I can't seem to figure out why. It seems to not be able to pick up certain part #'s in my Vlookup Table. It works for the form control combobox I still have in my sheet, but not for the ActiveX combobox that I just created. I made no changes to the data that these cells are linked to, so what could be the problem?
The reason Vlookup would return NA is when it can not find the part number in your data. Since your dropdown is using your data as the source for the list (my assumption), then I would expect that the Vlookup should find the matching value.
If it isn't, then you need to be able to see what is returned in C1 by the Combobox. Then you need to physically look at your data and find that value in the data.
Say you found the matching value at row 20. Then in D1 you could put
if that returns False, then you need to figure out why. So basically you have to identify where you are having problems and figure out why. Is the combobox not returning the full value?
I am not sure I can surmise much else. I could look at the workbook if it is small enough to email - tell me a part number that causes the problem and send to firstname.lastname@example.org