Returning multiple values for a lookup

umasskid4

New Member
Joined
Aug 6, 2015
Messages
5
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
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,215,053
Messages
6,122,888
Members
449,097
Latest member
dbomb1414

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top