VLOOKUP return all possible values (not unique)

Question

I need an array formula or VBA code that will work like a vlookup but will return all possible values instead of just the first value.

If the lookup value corresponds to 5 unique values, I need the code to show all 5 values.

We can assume the lookup values are going down Column A, the lookup range is columns F:G (with the 5 unique values in column G).

How do you expect these values to be returned? - Concatenated within 1 cell, or as an array? And what code have you tried so far? — Greedo
Returned as an array. I have strictly tried array formulas with index/match. — urdearboy
I have done this both with formula and a VBA code. With formulas, it will be really hard and it will put limitations, you need to go with the VBA solution. Look it up and you will find some stuff, unfortunately, I cannot just write the code for you here since this is not a write-me-the-code forum, but a Q & A one — Ibo
Understandable. I am still in the process of learning how to write the code and a few are up to write solution for rep and i'm hoping one of them stumbles across this. I agree that a VBA solution here is ideal. — urdearboy
You can combine COUNTIF($A$2:A2,A2) with the Index + Match formula found here How To Vlookup Find The First, 2nd Or Nth Match Value In Excel . The COUNTIF formula I provided is based off the running total pattern: Calculate a running total of a column of cells in Excel — user6432984

Subodh Tiwari sktneer Subodh Tiwari sktneer · Accepted Answer · 2017-10-18T18:39:13

Give this User Defined Function a try...

Function CustomVLookup(ByVal Lookup_Val As Range, Table_Array As Range, ColIndex As Integer) As String
Dim x, dict
Dim i As Long
x = Table_Array.Value
Set dict = CreateObject("Scripting.Dictionary")
For i = 1 To UBound(x, 1)
    If x(i, 1) = Lookup_Val.Value Then
        dict.Item(x(i, ColIndex)) = ""
    End If
Next i
If dict.Count > 0 Then
    CustomVLookup = Join(dict.keys, ", ")
Else
    CustomVLookup = ""
End If
End Function

And then use it on the sheet like below...

=CustomVLookup(A2,F2:G25,2)

Where A2 is the cell which contains the lookup value, F2:G25 is the table array and 2 is the column index which is 2 i.e. column G in this case.

Edit: The above function will return all the unique values which belong to the lookup value. If you want to return all the values not unique only, you will need a different approach. Let me know if that is the case.

VLOOKUP return all possible values (not unique)

1 Answers