Data:
A : B:
Wilbraham John Wagner (first row, row 1)
Wilbraham Casey Smith
Wilbraham Chris Kavanagh
Wilbraham Josh Gorman
Springfield Tom White
Springfield Elizabeth Crowl
I want to be able to lookup wilbraham and receive the the four names that correlate with it and then populate a text box with those four names. The lookup value would be "wilbraham" which is a value from "ComboBox1.Value" I am having a user select wilbraham on the combobox list and then this would search for the four names and display them on the textbox.
So the textbox on my spread would read like this
Town Names:
John Wagner
Casey Smith
Chris Kavanagh
Josh Gorman
I have been trying for hours, i got a Vlookup sucessfully going but realized it could only return the first name John Wagner. Now i'm trying to do a index-match but i'm lost.I feel like i was closer with the Vlookup as i successfully got John Wagner in the textbox and now my code just looks not even close.
I'd really appreciate any help!
first code for attempt at index match
Dim name As Variant
Dim reference As Variant
Dim row_num As Double
Dim column_num As Variant
Dim area_num As Variant
Dim lookupvalue As Variant
lookupvalue = CVar(ComboBox1.Value)
reference = Sheets("Names").Range("B1:B500")
row_num = Application.WorksheetFunction.Match(lookupvalue, Sheets("Employees").Range("A1:B1"), 0)
column_num = 1
area_num = 1
name = Application.WorksheetFunction.Index(reference, row_num, column_num, area_num)
peopleList.Text = "Town Names:" & vbNewLine & name
A : B:
Wilbraham John Wagner (first row, row 1)
Wilbraham Casey Smith
Wilbraham Chris Kavanagh
Wilbraham Josh Gorman
Springfield Tom White
Springfield Elizabeth Crowl
I want to be able to lookup wilbraham and receive the the four names that correlate with it and then populate a text box with those four names. The lookup value would be "wilbraham" which is a value from "ComboBox1.Value" I am having a user select wilbraham on the combobox list and then this would search for the four names and display them on the textbox.
So the textbox on my spread would read like this
Town Names:
John Wagner
Casey Smith
Chris Kavanagh
Josh Gorman
I have been trying for hours, i got a Vlookup sucessfully going but realized it could only return the first name John Wagner. Now i'm trying to do a index-match but i'm lost.I feel like i was closer with the Vlookup as i successfully got John Wagner in the textbox and now my code just looks not even close.
I'd really appreciate any help!
first code for attempt at index match
Dim name As Variant
Dim reference As Variant
Dim row_num As Double
Dim column_num As Variant
Dim area_num As Variant
Dim lookupvalue As Variant
lookupvalue = CVar(ComboBox1.Value)
reference = Sheets("Names").Range("B1:B500")
row_num = Application.WorksheetFunction.Match(lookupvalue, Sheets("Employees").Range("A1:B1"), 0)
column_num = 1
area_num = 1
name = Application.WorksheetFunction.Index(reference, row_num, column_num, area_num)
peopleList.Text = "Town Names:" & vbNewLine & name
Last edited: