200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
In Sheet1 I have created a table called 'Customers' with two columns as name(col A) and age(col B).
In Sheet2 I have a combo box (in properties it is assigned to B2).
When the user searches in the combo box and selects a name then I want in cell B3 the correspondent age to be auto filled.
A solution is contained in the attached workbook. It starts by solving the problem of the list for your combo box. The best way is to used a named range because it's easy to make it dynamic. I chose to create a single range for names and ages. I called it "Customers" but any other name will be just as fine.
The combo box only needs the name column but the VLOOKUP needs both. In order to avoid having two ranges I changed the combo box to have two columns as well but hide the second column. Details how to set the Cbx properties are given on Sheet2.
Now your Cbx shows the list of names and, because it's linked to B2, that cell shows any name selected in the Cbx. With that arranged, we can now use the value of B2 to look up the age in the "Customers" range. Enter this formula in B3.
=VLOOKUP(B2,Customers,2,FALSE)