0

I have an excel sheet with a list of names in one column and some associated values in the next column. Some of these names appear several times with different values in the next column.

For example:

John  AA
James AE
Trent TE
Jim   CE
Trent AX
Jim   XC

The result I'm looking to return from my vlookup function is:

John  AA
James AE
Trent TE AX
Jim   CE XC

Yet vlookup can only return one value, is there another function that can help me with this problem?

2 Answers 2

1

I would like to suggest the following solution, just check Screen shot.

enter image description here

Source Data Range is A1:B7 (excluding Headers).

Result Data Range is A11:B14.

First create a Unique Name List, write this Formula in Cell A11.

{=IFERROR(INDEX($A$2:$A$7,MATCH(0,COUNTIF($A$11:A11,$A$2:$A$7),0)),"")}

For the final Results write Formula in B11.

{=IFERROR(INDEX($B$2:$B$7, SMALL(IF($A11=$A$2:$A$7, ROW($A$2:$A$7)-ROW($A$2)+1), COLUMN(A1))),"")}

Drag the Formula one Cell Write then Down till is required.

Hope this help you, I've posted the Solution after is been tested by me.

3
  • Hi thank you for your response, I've tried the code above and had a circular reference error, not sure if there is something wrong with my version of excel? would you be able to send me a link to your copy of the excel file with the working code? Thanks.
    – IamTrent
    Nov 21, 2017 at 1:48
  • My Screen Shot is part of Excel Sheet I've simply copied and paste in Paint to make a Png file. The same one I used to load here with my answers. Cud you suggest me how to load the file Link, since I've never used it before. Nov 21, 2017 at 6:23
  • Maybe put it on Dropbox or Google storage and share a link to it?
    – IamTrent
    Nov 22, 2017 at 5:43
0

Nevermind found some code that works very well.

Option Explicit
Function LookupCSVResults(lookupValue As Variant, lookupRange As Range, 
resultsRange As Range) As String

Dim s As String 'Results placeholder
Dim sTmp As String  'Cell value placeholder
Dim r As Long   'Row
Dim c As Long   'Column
Const strDelimiter = "|||"  'Makes InStr more robust

s = strDelimiter
For r = 1 To lookupRange.Rows.Count
    For c = 1 To lookupRange.Columns.Count
        If lookupRange.Cells(r, c).Value = lookupValue Then
            'I know it's weird to use offset but it works even if the two ranges
            'are of different sizes and it's the same way that SUMIF works
            sTmp = resultsRange.Offset(r - 1, c - 1).Cells(1, 1).Value
            If InStr(1, s, strDelimiter & sTmp & strDelimiter) = 0 Then
                s = s & sTmp & strDelimiter
            End If
        End If
    Next
Next

'Now make it look like CSV
s = Replace(s, strDelimiter, ",")
If Left(s, 1) = "," Then s = Mid(s, 2)
If Right(s, 1) = "," Then s = Left(s, Len(s) - 1)

LookupCSVResults = s 'Return the function

End Function

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .