VLOOKUP with multiple criteria returning values in one cell

Question

I found this VBA that is capable to return all matching values into one cell using one criteria to match:

Function MYVLOOKUP(pValue As String, pWorkRng As Range, pIndex As Long)
'Update 20150310
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
    If rng = pValue Then
        xResult = xResult & " " & rng.Offset(0, pIndex - 1)
    End If
Next
MYVLOOKUP = xResult
End Function

But I need this VLOOKUP to return values compared to multiple matching criteria.

Any ideas how this could be upgraded?

Thanks. Update below:

Data table:

I need formula to return values in one cell where A1-1A and A.0002 matches. Outcome should be 8 3

Medius

Can you give us some examples // what does your data look like? – Blim

Answer 1

Here is a slightly different approach.

Function TEXTJOIN(delim As String, skipblank As Boolean, arr)
    Dim d As Long
    Dim c As Long
    Dim arr2()
    Dim t As Long, y As Long
    t = -1
    y = -1
    If TypeName(arr) = "Range" Then
        arr2 = arr.Value
    Else
        arr2 = arr
    End If
    On Error Resume Next
    t = UBound(arr2, 2)
    y = UBound(arr2, 1)
    On Error GoTo 0
    
    If t >= 0 And y >= 0 Then
        For c = LBound(arr2, 1) To UBound(arr2, 1)
            For d = LBound(arr2, 1) To UBound(arr2, 2)
                If arr2(c, d) <> "" Or Not skipblank Then
                    TEXTJOIN = TEXTJOIN & arr2(c, d) & delim
                End If
            Next d
        Next c
    Else
        For c = LBound(arr2) To UBound(arr2)
            If arr2(c) <> "" Or Not skipblank Then
                TEXTJOIN = TEXTJOIN & arr2(c) & delim
            End If
        Next c
    End If
    TEXTJOIN = Left(TEXTJOIN, Len(TEXTJOIN) - Len(delim))
End Function

It allows you to determine the delimiter, as in you can have , or just a space or anything you want to put between the return values.

The second criteria asks if you want to return an empty space for any that are empty.

The third you would put an array form of IF() that uses the criteria you want to filter the return values.

So in your instance you would use this in array form:

=TEXTJOIN(" ",TRUE,IF((A2:A7="A")*(B2:B7=2),C2:C7,""))

The " " says we want a space between the values.

The TRUE means we skip any blanks, this is important as we send blanks when the values are not justified by the filter.

the IF((A2:A7="A")*(B2:B7=2),C2:C7,"") cycles through the columns and returns the values when both Boolean tests are TRUE, If not it returns a blank.

Being and array formula it must be confirmed with Ctrl-Shift-Enter when exiting edit mode instead of Enter. If done correctly then Excel will put {} around the formula.

---

If you wanted to return the full column you could simply use:

=TEXTJOIN(" ",TRUE,C2:C7)

In regular form and it would return 8 3 3 9 2 3 in one cell.

---

NOTE

If you have Office 365 Excel TEXTJOIN is a formula that exists natively that is entered like above in both cases.

Office 365 also has FILTER and we can use:

=TEXTJOIN(" ",TRUE,FILTER(C2:C7,(A2:A7="A")*(B2:B7=2),""))
 

Chapatti

Would it make a difference if columns are mixed up in order? As the current order stands A B C what if it would be C A B ? – Medius

@Medius not one bit just change the ranges to match. And make sure you enter it as an array with Ctrl-Shift-Enter instead of enter. – Chapatti

Which option does it take longer to calculate? Using VBA or formula on itself? Which one would you suggest, as I'm looking forward to work with approximately 15k-20k rows. – Medius

If you have Office 365 excel then use just the formula. If you do not then I would use as I have shown above. Do not use full column references, but limit the references to the actual dataset. – Chapatti

@ScottCraner I'm stumbling into this post years later, but I typically scope input ranges to be an intersect with Range's worksheet's Usedrange. That enables the user to select full columns without too much performance (unless they have a huge number of rows). So for the range line.... arr2 = Intersect(arr,arr.Worksheet.UsedRange).Value – Pentad