Getting VLookup to Return Multiple Values? Help Please!

[jameshunt]

Hello Everyone,
Great boards!

I have searched but I do not know how to make Vlookup return multiple values instead of just the first one it finds in the column. My data looks as follows


Apples 10
Banana 5
Apples 10
Apples 10
Banana 5
Banana 5
Apples 10
Apples 10

And I'd like to grab all the Apples values with a Vlookup or similar function but of course Vlookup will only return the first value of 10.

Thanks a lot for your help!

James

 

[reggie_V]

James

To add all the values that are next to the word "apples" you can use the SUMIF function e.g. (if your data is in columns A and B):

=sumif(a1:a8,"Apples",b1:b8)

It looks down column A and when it finds "Apples" it goes across to column B and ultimately returns the total of all the applicable values in col. B

As far as I recall it's not case-sensitive.

Hope this helps.

Reggie

 

[Jon von der Heyden]

Hi,

Holy Macro has a solution to this. Is it ok to post their solutions on the board?

Place this code in a standard module. Are you familiar with UDF's? Instead of VLOOKUP you would now write =sSReference("Apples",A1:B10) where column A houses the fruit and column B houses the values you want to return.

Function sSReference(strName As String, rngField As Range) As Variant
   Dim rngAct As Range
   Dim strResult As String
   Application.Volatile
   For Each rngAct In rngField.Columns(1).Cells
      If rngAct = strName Then
         strResult = strResult & rngAct.Offset(0, 1) & Chr(10)
      End If
   Next rngAct
   sSReference = Left(strResult, Len(strResult))
End Function

Regards,
Jon