Getting VLookup to Return Multiple Values? Help Please!

jameshunt

New Member
Joined
Oct 18, 2006
Messages
1
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
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

reggie_V

New Member
Joined
Aug 15, 2005
Messages
3
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

MrExcel MVP, Moderator
Joined
Apr 6, 2004
Messages
10,810
Office Version
  1. 365
Platform
  1. Windows
Hi,

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

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.

Code:
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 :)
 

Forum statistics

Threads
1,141,097
Messages
5,704,314
Members
421,338
Latest member
Pepess

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top