Vlookup, return multiple values to a cell

Question

is there anyway to return multiple values from a vlookup? I would like col I in Sheet 1 to return multiple values to a cell, or is there another way of displaying this (would rather not pivot)?

Sheet 1 : has all my unique values (Col F, and returning values in Col I),

Sheet 3: Col A has duplicate string values which correspond to unique strings in Col B which are unique, including blanks.

EDIT

Sheet 1 or desired result :

Sheet 1: Current

Sheet 3 Current:

Current formula

=VLOOKUP(F2,Sheet3!A:B,2,FALSE)

Returns mostly 0's, due to the blanks or multiple values corresponding to the unique values.

Can you put a pic on? Can't work out from your description what is in in Sheet3 Column B and Sheet1 Column F. — Tim Edwards
That helps in that I now understand what you're after. I think I've seen it done somewhere else on the site but I can't remember where. I don't think it used VLOOKUP though, definitely an array formula. — Tim Edwards
This is similar. I'm guessing if you want to concatenate them into a cell then that should be an extra step. Alternative is through VBA but as you've not put that tag I don't know whether you'd want that. — Tim Edwards
Thanks Tim, First 2 answers are not returning me any values. Does the vba solution have to be modified? I do CSVLookup ( lookup value, Col B sheet 3, Col A sheet 3) , and get blanks in my sheet. — Jonnyboi

Tim Edwards Tim Edwards · Accepted Answer · 2016-08-09T09:25:29

In terms of VBA then, you have to change the code a bit from what was in the link I sent you. This should work:

Option Explicit
Function vlookupmulti(rngLookup As Variant, rngSource As Range, col As Double) As String
Dim d As Double, strCell As String

'Error if range has less columns than col
If rngSource.Columns.Count < col Then
    vlookupmulti = CVErr(xlErrNA)
    Exit Function
End If

'Loop through rows in the lookup column
For d = rngSource.Row To rngSource.Rows.Count
    If rngLookup = Sheets(rngSource.Parent.Name).Cells(d, rngSource.Column).Value Then
        strCell = Sheets(rngSource.Parent.Name).Cells(d, rngSource.Column + col - 1).Value
        If Len(strCell) > 0 Then vlookupmulti = vlookupmulti & strCell & ", "
    End If
Next d

'Remove comma at end
If Right(vlookupmulti, 2) = ", " Then
    vlookupmulti = Left(vlookupmulti, Len(vlookupmulti) - 2)
End If

'Give error if no results
If vlookupmulti = "" Then
    vlookupmulti = CVErr(xlErrNA)
End If

End Function

Vlookup, return multiple values to a cell

1 Answers