VLOOKUP Alternative-Multiple Vales

[silverlucky5]

Hi All,
Hoping someone can help me out
I have the following lookup table.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Branch[/TD]
[TD]City[/TD]
[TD]Material[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Paper[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Cloth[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Wood[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Branch2[/TD]
[TD]Boston[/TD]
[TD]Cardboard[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Branch3[/TD]
[TD]Dallas[/TD]
[TD]Plastic[/TD]
[/TR]
</tbody>[/TABLE]











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.)
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lookup Value[/TD]
[TD]Result1[/TD]
[TD]Result2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Branch2[/TD]
[TD]Boston[/TD]
[TD]Cardboard[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Branch3[/TD]
[TD]Dallas[/TD]
[TD]Plastic[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Paper[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Atlanta[/TD]
[TD]Cloth[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]Atlanta[/TD]
[TD]Wood[/TD]
[/TR]
</tbody>[/TABLE]











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.
[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Lookup Value[/TD]
[TD]Result1[/TD]
[TD]Result2[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Branch2[/TD]
[TD]Boston[/TD]
[TD]Cardboard[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Branch3[/TD]
[TD]Dallas[/TD]
[TD]Plastic[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Paper[/TD]
[/TR]
</tbody>[/TABLE]








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

 

[Toadstool]

Silverlucky5,

I believe I may have a solution for you. Please watch this video and let me know if it satisfies your requirement.

https://www.youtube.com/watch?v=ReYH-FMUa04



Regards,
Toadstool

 

[Fluff]

@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

 

[Toadstool]

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


[TABLE="class: grid, width: 887"]
<colgroup><col span="2"><col><col><col><col><col><col span="2"><col><col><col></colgroup><tbody>[TR]
[TD="align: center"][/TD]
[TD="align: center"]A
[/TD]
[TD="align: center"]B
[/TD]
[TD="align: center"]C
[/TD]
[TD="align: center"]D
[/TD]
[TD="align: center"]E
[/TD]
[TD="align: center"]F
[/TD]
[TD="align: center"]G
[/TD]
[TD="align: center"]H
[/TD]
[TD="align: center"]I
[/TD]
[TD="align: center"]J
[/TD]
[TD="align: center"]K
[/TD]
[TD="align: center"]L
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD]Branch
[/TD]
[TD]City[/TD]
[TD]Material[/TD]
[TD][/TD]
[TD]Lookup Result
[/TD]
[TD]Result1
[/TD]
[TD]Result2
[/TD]
[TD]Result Line[/TD]
[TD]Row per Branch
[/TD]
[TD][/TD]
[TD]Lookup Value
[/TD]
[TD]Count of Lookup
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Paper[/TD]
[TD][/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Wood[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD][/TD]
[TD]Branch1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Cloth[/TD]
[TD][/TD]
[TD]Branch1
[/TD]
[TD]Atlanta[/TD]
[TD]Cloth[/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD][/TD]
[TD]Branch2[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]4
[/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Wood[/TD]
[TD][/TD]
[TD]Branch1[/TD]
[TD]Atlanta[/TD]
[TD]Paper[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Branch3[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD="align: center"]5
[/TD]
[TD]Branch2[/TD]
[TD]Boston[/TD]
[TD]Cardboard[/TD]
[TD][/TD]
[TD]Branch2[/TD]
[TD]Boston[/TD]
[TD]Cardboard[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD][/TD]
[TD]Branch99[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD="align: center"]6
[/TD]
[TD]Branch3[/TD]
[TD]Dallas[/TD]
[TD]Plastic[/TD]
[TD][/TD]
[TD]Branch3[/TD]
[TD]Dallas[/TD]
[TD]Cloth[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]7
[/TD]
[TD]Branch4[/TD]
[TD]New York[/TD]
[TD]Paper[/TD]
[TD][/TD]
[TD]Branch3[/TD]
[TD]Dallas[/TD]
[TD]Plastic[/TD]
[TD]3[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]8
[/TD]
[TD]Branch3[/TD]
[TD]Dallas[/TD]
[TD]Cloth[/TD]
[TD][/TD]
[TD]Branch99[/TD]
[TD]Miami[/TD]
[TD]Cloth[/TD]
[TD]4[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]9
[/TD]
[TD]Branch5[/TD]
[TD]Atlanta[/TD]
[TD]Stone[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]0[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]10
[/TD]
[TD]Branch99[/TD]
[TD]Miami[/TD]
[TD]Cloth[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-1[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]11
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-2[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]12
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-3[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]13
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-4[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]14
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-5[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]15
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-6[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]16
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-7[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD="align: center"]17
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]5[/TD]
[TD]-8[/TD]
[TD][/TD]
[TD][/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[silverlucky5]

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.

 

[Toadstool]

You're welcome!
I'm between paying gigs at the moment so thought I'd do some pro bono work.

 

[Peter_SSs]

...this is EXACTLY the result I was looking for ...

First, just a word of warning.
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
	A	B	C	D	E	F	G	H	I	J
1	Branch	City	Material		Lookup Result	Result1	Result2		Lookup Value	1
2	Branch1	Atlanta	Paper		Branch2	Boston	Cardboard		Branch2	1
3	Branch1	Atlanta	Cloth		Branch3	Dallas	Plastic		Branch3	2
4	Branch1	Atlanta	Wood		Branch3	Dallas	Cloth		Branch1	4
5	Branch2	Boston	Cardboard		Branch1	Atlanta	Paper			7
6	Branch3	Dallas	Plastic		Branch1	Atlanta	Cloth
7	Branch4	New York	Paper		Branch1	Atlanta	Wood
8	Branch3	Dallas	Cloth
9	Branch5	Atlanta	Stone
10	Branch99	Miami	Cloth
Lookup

 

[silverlucky5]

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?

 

[Peter_SSs]

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.

 

[silverlucky5]

Oh yes, very sorry, my bad. Works great! thank you Peter!!