0

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 :

enter image description here

Sheet 1: Current

enter image description here

Sheet 3 Current:

enter image description here

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.

Jonnyboi
  • 505
  • 5
  • 19
  • 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 Aug 08 '16 at 15:13
  • Hi Tim, does this help? – Jonnyboi Aug 08 '16 at 15:24
  • 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 Aug 08 '16 at 15:49
  • [This](http://superuser.com/questions/536234/excel-how-to-vlookup-to-return-multiple-values) 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 Aug 08 '16 at 15:54
  • 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 Aug 08 '16 at 17:47

1 Answers1

1

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
Tim Edwards
  • 1,031
  • 1
  • 13
  • 34
  • Thanks for your reply Tim, how do I format the formula in the sheet? i.e `vlookupmulti = (lookup value, ___,__)` – Jonnyboi Aug 09 '16 at 15:22
  • Same as a normal `VLOOKUP` but I haven't got the last TRUE/FALSE argument. – Tim Edwards Aug 09 '16 at 15:23
  • it works :), is there anyway to make it run faster? Seems to be freezing my excel. – Jonnyboi Aug 09 '16 at 15:44
  • It'll do that if you have a large range. That's the drawback of using this method. You could turn calculation to manual and then only calculate this when necessary. I guess you could put `Application.ScreenUpdating = False` at the start and `Application.ScreenUpdating = True` before both the `Exit Function` line and the `End Function` line but I don't think that would drastically change things. – Tim Edwards Aug 09 '16 at 15:47
  • I've given a non-VBA answer to this sort of thing before [here](http://stackoverflow.com/questions/38476499/excel-how-to-use-an-array-to-concatenate-strings-that-are-next-to-a-criteread-s) but that will put your options in separate cells. That will have the benefit of not locking up excel quite as much. – Tim Edwards Aug 09 '16 at 15:50