Excel 2013 VBA Find data in table to populate a label

[JasonEnsor]

Hi Guys,

I have a table in Excel 2013, that has 2 columns (Team Lead, Team Name)

I am using the values from Team Lead to populate a combobox

teamLeads.List = Worksheets("Sheet1").ListObjects(1).ListColumns(1).DataBodyRange.Value

what I am wanting is that once a value is chosen from the combobox it populates a label with the corresponding team name. Is there a better way of doing this that looping through the excel column checking every value? Not sure if there is a way of restricting the search to just looking at the table column then returning an offset value (the next column over)

the layout of my table is

Team Lead | Team Name
Jason | Team 1
Tom | Team 2
Bob | Team 3

So if Jason was picked from the Combobox I would want the label to show Team 1

Any ideas or advice would be appreciated.

Regards

J/

Regards

J.

 

[SkipVought]

Hi,

What kind of control: Forms, AcyiveX, Data Validation?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[JasonEnsor]

Hi Skip,

This the combobox is on a userform. There is no data validation setup for the drop downs

Regards

J.

 

[SkipVought]

Use the MATCH() function to find the offset in the list and then the INDEX() function to return the corresponding value in that offset row.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]