Vlookup with multiple values

[johnny52]

Lets say I have a worksheet consisting of 3 columns and 3 rows

row a1=NY row a2 =Tex row a3 =Tex
row b1=1 row b2=2 row b3 =3
row c1=Tex c2=Fla row c3= NY

my question is I would like to use a vlookup or match or an idex formula in D1 to return multiple values

if I use vlookup(c1,a1:b3,2,false) in col d1 i get the results 2 ( the first value it finds)

I would like a formula showing the Tex value from the entire range in one cell so in d1 the results would be 2,3

Is it possible.......Thanks everybody

 

[sandy666]

with PowerQuery (Get&Transform)

Column1	Column2	Column1		Column1	Result
NY	1​	Tex		Tex	2, 3
Tex	2​	Fla		Fla
Tex	3​	NY		NY	1

[SIZE=1]// Result
let
    tbl1 = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    tbl2 = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Source = Table.NestedJoin(tbl2,{"Column1"},tbl1,{"Column1"},"tbl1",JoinKind.LeftOuter),
    List = Table.AddColumn(Source, "Result", each Table.Column([tbl1],"Column2")),
    Extract = Table.TransformColumns(List, {"Result", each Text.Combine(List.Transform(_, Text.From), ", "), type text})
in
    Extract[/SIZE]

 

[johnny52]

that macro works....great

would there be a formula available as well....I want to use it on multiple worksheets,with varying parameters

 

[johnny52]

=IFERROR(INDEX($B$3:$B$13,SMALL(IF($C3=$A$3:$A$13,ROW($B$3:$B$13)-2,""),COLUMN()-4)),"")

ok I found an array formula that does the trick the data ranges are a bit different....but they can be modified........Thanks for all your help