VBA - how to get ALL matching results using MATCH() or VLOOKUP()

Hey again,

I have a calendar-type .xls going on where I use the calendar templates for each month with added worksheets for all the notes/orders data and a helper worksheet on the side to make things smoother. Its operated by a userform which draws data from the data worksheet from any given date.

Now what I would like to do is to get a button which would get the order data for each date (just the shipment location and name of the company/person) from another .xls-file where I have the orders neatly organized in rows with plenty of information of each one (organized in columns).

The problem is that MATCH/INDEX or VLOOKUP only return the first value encountered. A lot of dates have multiple shipments going off. How do I work around this? My mind is toying around with WHILE loops but I don't know enough about VBA programming to figure out how to ignore already returned results the second time around.

Oh and the solution doesn't have to have anything to do with MATCH or VLOOKUP it was just that I figured that would be the easiest approach.

bumpy bump

Could you clarify a little further, please?

When you press the button do you want it to return all of the matches at once, or do you want it to produce the next match each time the button is pressed?

Originally Posted by Andrew-R

Could you clarify a little further, please?

When you press the button do you want it to return all of the matches at once, or do you want it to produce the next match each time the button is pressed?

Preferably all at once.

No need to get into the layout and formatting rows, just the general idea behind getting the commands to return ALL values instead of one/first they stumble upon.

The following function will return all of the matches as one string:

Function LookUpAll(varFind As Variant, rngSearchArea As Range, intReturnCol As Integer, Optional strDelimiter = ",") As Variant

Dim rngFindItem As Range
Dim strFirstAddress As String
Dim varTempRet

varTempRet = ""

Set rngFindItem = rngSearchArea.Find(varFind, lookat:=xlWhole)

If rngFindItem Is Nothing Then
  varTempRet = "#N/A"
Else
  strFirstAddress = rngFindItem.Address
  
  While Not rngFindItem Is Nothing
    varTempRet = varTempRet & rngFindItem.Offset(0, intReturnCol - 1).Value & strDelimiter
    Set rngFindItem = rngSearchArea.FindNext(rngFindItem)
    If rngFindItem.Address = strFirstAddress Then
      Set rngFindItem = Nothing
    End If
  Wend
  
  varTempRet = Left(varTempRet, Len(varTempRet) - Len(strDelimiter))
  
End If

LookUpAll = varTempRet

End Function

The syntax to use it is:

LookUpAll(Value To Find, Range To Search, Column to Return,[Value delimiter])

Does that get you started?

Yes, I think I can use it to put the columns into the helper-sheet and then pull the data from there into the data sheet in rows.
I haven't created my own functions before, should I create a new module for it to insert the code into the beginning of the userform?

You can put it in an existing module or create a new one for it.

It *should* work as a worksheet function ... yet it doesn't and I don't know why, so you'll have to embed it in some code. Not a problem if you're using a user form.