You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- vlookup combo box

Advertisement

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 3.1.2.1. Enclosures shows up.

I attached an image to give you a better idea!

Thank you for all of your help!

ANSWER: Kathryn,

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

=if(C1="","",vlookup(Index($A$2:$A$500,C1,1),A:B,2,False))

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 3.1.2.1 Enclosures from column B.

If you use the ActiveX Combobox, it will return the part number so you could avoid the use of Index.

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Tom,

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?

Thank you!

ANSWER: Kathryn,

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:

instead of

E1: =if(C1="","",vlookup(Index($A$2:$A$500,C1,1),A:B,2,False))

you would do

E1: =if(C1="","",vlookup(C1,A:B,2,False))

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)

--

Regards,

Tom Ogilvy

---------- FOLLOW-UP ----------

QUESTION: Tom,

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?

Kathryn,

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

=C1=A20

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 twogilvy@msn.com

--

Regards,

Tom Ogilvy

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Extensive experience. **Education/Credentials**

Master of Science (MS) degree Operations Research (ORSA)**Awards and Honors**

Microsoft MVP in Excel.