MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Combo Box


Posted by Kevin Armstrong on July 09, 2001 2:00 PM

I have a combo box containing a listing of account numbers & names pulled from columns 1 & 2 of a table in another tab. The output is the account number to a cell on the first tab. I want to use this output cell (account number) and a vlookup function to populate the rest of this spreadsheet with other data in columns 3 - 6 of the other tab.

The problem is that the lookup function is not recognizing the number in the output cell as matching the number in the table (even though they are the same number. Tried changing the format to text, numbers, but no luck.

Help please!


Posted by Aladin Akyurek on July 09, 2001 2:12 PM

Try:

=VLOOKUP(cell-containing-acc-num+0,lookup-table,column,0)

where the formula expects acc-number of consisting solely of digits.

Aladin

Posted by Mark W. on July 09, 2001 4:23 PM

Kevin, there's really no need for a VLOOKUP() to
accomplish your desired results. After all, the
combo box already gives you the row number you
need!

Suppose that Sheet1!A1:C4 contains...

{"Field1","Field2","Field3"
;"A123","Bob",10
;"B234","Tom",20
;"CX23","Harry",30}

...and, the input range for your combo box is
Sheet1!A2:A4... and your cell link is Sheet2!C1.

Then the array formula...

{=INDEX(Sheet1!A2:C4,Sheet2!C1,0)}

...entered into cells Sheet2!A5:C5 should do the
trick!