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
– Greedo, Commented Oct 18, 2017 at 18:29
Returned as an array. I have strictly tried array formulas with index/match. — urdearboy
– urdearboy, Commented Oct 18, 2017 at 18:38
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
– Ibo, Commented Oct 18, 2017 at 19:09
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
– urdearboy, Commented Oct 18, 2017 at 19:18
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
– user6432984, Commented Oct 18, 2017 at 19:24

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

1

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.

answered Oct 18, 2017 at 18:39

Subodh Tiwari sktneer

9,9862 gold badges20 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

urdearboy Over a year ago

urdearboy Over a year ago

The setup would look like this with the output going down column B. I'm sorry this wasn't clear above.

user6432984 Over a year ago

There is no need for the last If statement. Join(dict.keys, ", ") returns "" if the dict.Count = 0. Of course it might be slightly more efficient to test if dict.Count = 0 but in that case you can eliminate the Else block because `CustomVLookup returns "" if you don't assign it a value. Nice work +1.

Subodh Tiwari sktneer Over a year ago

@ThomasInzina I agree. The possibilities are endless. :)

Subodh Tiwari sktneer Over a year ago

@ThomasInzina Thanks Lol

Collectives™ on Stack Overflow

VLOOKUP return all possible values (not unique)

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related