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!

 

[Peter_SSs]

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

You're welcome.

 

[silverlucky5]

Also, another caution. Because of some of the absolute references, you can't just add a new row to the bottom of the source table or add a lookup value beyond row 10. I will experiment with removing the absolute references in question and see if that works. In the meantime, my workaround is to cut and paste the "last" rows down and adding the new values above it.

 

[Peter_SSs]

.. you can't just add a new row to the bottom of the source table or add a lookup value beyond row 10.

Here are 2 solutions to that using the same sort of method I used above.

Method A
With exactly the same layout as before ..
i) Change all $10 references in the formulas in E2, F2 and J2 to 100 or 1000 or whatever is a 'reasonable' number to ensure it will cover any expansion you might do without going overboard.
ii) E2 and J2 are copied down "as far as you might ever need"
iii) F2 is copied across to G2 and down to the same row you copied E2 & J2


Method B
Still the same layout as before, but ..
i) Select all the data currently in columns A:C (that would be A1:C10 in the sample given previously).
ii) On the Insert ribbon tab choose Table -> My table has headers -> OK
iii) J1 still holds a manually entered 1
iv) All of the formulas shown below are copied down "as far as you might ever need"

Now, if you add extra data in columns A:C, the table will automatically expand to include that data & the formulas will also include that extra 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
11
Lookup (2)