VLOOKUP Alternative-Multiple Vales

silverlucky5

New Member
Joined
Sep 8, 2009
Messages
35
Hi All,
Hoping someone can help me out :)
I have the following lookup table.
ABC
1BranchCityMaterial
2Branch1AtlantaPaper
3Branch1AtlantaCloth
4Branch1AtlantaWood
5Branch2BostonCardboard
6Branch3DallasPlastic

<tbody>
</tbody>











I want to look up the values shown in "Lookup Value" column below and return results (from Lookup Table) as shown below. (FYI: There doesn't necessarily have to be a blank in "Lookup Value" column for the other 2 "Atlanta" results and the multiple results don't have to be in any particular order.)
ABC
1Lookup ValueResult1Result2
2Branch2BostonCardboard
3Branch3DallasPlastic
4Branch1AtlantaPaper
5AtlantaCloth
6AtlantaWood

<tbody>
</tbody>











I know VLOOKUP will only return the first value of Atlanta as shown below. I have tried all kinds of formulas with MATCH and SMALL and INDEX but I'm not having too much luck. :eek:
ABC
1Lookup ValueResult1Result2
2Branch2BostonCardboard
3Branch3DallasPlastic
4Branch1AtlantaPaper

<tbody>
</tbody>








Does anyone know how I would create a formula (preferred) or a macro to do this?
Thank you!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
@Toadstool
Can you please post your suggestions to the board, as per rule#4

please do not answer questions by creating solutions elsewhere and then referencing those solutions via file links. We ask that you answer the question within the thread itself (note: providing links to existing articles on the internet may be acceptable if not breaching other rules).
Many thanks
 
Upvote 0
Oh dear!
I can't see a way to edit my original response so let me add a textual description. My example just uses the first 17 rows of data.

For my proposed solution the first three columns are the source data to be searched.

The penultimate column "Lookup Value" is where the search terms should be entered.

A worker column is added "Count of Lookup" to identify how many rows match in the source data for each Lookup Value.
=COUNTIF($A$2:$A$17,K2)

Worker columns "Result Line" and "Row per Branch" are added to keep a track of which Lookup Value to use and how many rows are left to display.
Result Line starts with a hard coded 1 then in the next row is =IF(I2=1,H2+1,H2) so if the countdown of rows to be retrieved meets 1 then we start with the next Lookup Value.

The "Row per Branch" is a countdown so when we reach 1 it gets the count of the next "Count of Lookup" for the "Lookup Value", otherwise it decrements the current Lookup Value row count. =IF(I2=1,INDEX($L$2:$L$17,H2+1),I2-1)


The "Lookup Result" is an INDEX to show which Lookup Value is currently being displayed.
=IF(INDEX($K$2:$K$17,H2)=0,"",INDEX($K$2:$K$17,H2))

The "Result1" and "Result2" are matched entries using INDEX and the AGGREGATE SMALL option to retrieve each matching entry.
=IF(E2="","",INDEX($B$2:$B$17,AGGREGATE(15,6,ROW($C$2:$C$17)-1/($A$2:$A$17=E2),I2)))
and
=IF(E2="","",INDEX($C$2:$C$17,AGGREGATE(15,6,ROW($C$2:$C$17)-1/($A$2:$A$17=E2),I2)))


A
B
C
D
E
F
G
H
I
J
K
L
1
Branch
CityMaterialLookup Result
Result1
Result2
Result LineRow per Branch
Lookup Value
Count of Lookup
2
Branch1AtlantaPaperBranch1AtlantaWood13Branch13
3
Branch1AtlantaClothBranch1
AtlantaCloth12Branch21
4
Branch1AtlantaWoodBranch1AtlantaPaper11Branch32
5
Branch2BostonCardboardBranch2BostonCardboard21Branch991
6
Branch3DallasPlasticBranch3DallasCloth320
7
Branch4New YorkPaperBranch3DallasPlastic310
8
Branch3DallasClothBranch99MiamiCloth410
9
Branch5AtlantaStone500
10
Branch99MiamiCloth5-10
11
5-20
12
5-30
13
5-40
14
5-50
15
5-60
16
5-70
17
5-80

<colgroup><col span="2"><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>
</tbody>
 
Upvote 0
Brilliant Toadstool! Thank you SO much for the time you spent working on this...this is EXACTLY the result I was looking for and while I know Mr. Excel doesn't want external links, the video perfectly explained your solution.
You have saved me hours upon hours of time and I appreciate more than you know.
:)
 
Upvote 0
You're welcome!
I'm between paying gigs at the moment so thought I'd do some pro bono work.
:cool:
 
Upvote 0
...this is EXACTLY the result I was looking for ...
First, just a word of warning.:eek:
If you, or another user, was to subsequently add any rows above this data, the results using the formulas above, or at least some of them, would suddenly become incorrect.

Second, here is an alternative approach that requires less helpers and also includes robustness against the issue mentioned above.
Column I holds your lookup values of interest.
J1 has a manually entered 1.
E2 and J2 are copied down to the bottom of your data, row 10 for this sample.
F2 is copied across to G2 and down to the bottom of the data.

Excel Workbook
ABCDEFGHIJ
1BranchCityMaterialLookup ResultResult1Result2Lookup Value1
2Branch1AtlantaPaperBranch2BostonCardboardBranch21
3Branch1AtlantaClothBranch3DallasPlasticBranch32
4Branch1AtlantaWoodBranch3DallasClothBranch14
5Branch2BostonCardboardBranch1AtlantaPaper7
6Branch3DallasPlasticBranch1AtlantaCloth
7Branch4New YorkPaperBranch1AtlantaWood
8Branch3DallasCloth
9Branch5AtlantaStone
10Branch99MiamiCloth
Lookup
 
Last edited:
Upvote 0
Aha Peter! I see. Thank you!! :) One quick question please. In cell J5, it looks like you have a simple SUM to add J2:J4. If I add additional lookup values to column I (say Branch5 and Branch99), seems that formulas will work only if I do a sum at the bottom of column J (starting with the row beneath the hard coded 1). Am I understanding you correctly?
 
Upvote 0
In cell J5, it looks like you have a simple SUM to add J2:J4. If I add additional lookup values to column I (say Branch5 and Branch99), seems that formulas will work only if I do a sum at the bottom of column J (starting with the row beneath the hard coded 1). Am I understanding you correctly?
No, I don't have a SUM function in J5. As described in my previous post, the formula in J2 is simply copied down the column to the last row of data in column A.

If you add Branch5 and Branch99 in cells I5 & I6 all the correct results should automatically appear in columns E:G.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,172
Members
448,554
Latest member
Gleisner2

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
Back
Top