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)

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]Column1[/td][td=bgcolor:#5B9BD5]Column2[/td][td=bgcolor:#5B9BD5]Column1[/td][td][/td][td=bgcolor:#70AD47]Column1[/td][td=bgcolor:#70AD47]Result[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]NY[/td][td=bgcolor:#DDEBF7]

1​

[/td][td=bgcolor:#DDEBF7]Tex[/td][td][/td][td=bgcolor:#E2EFDA]Tex[/td][td=bgcolor:#E2EFDA]2, 3[/td][/tr]

[tr=bgcolor:#FFFFFF][td]Tex[/td][td]

2​

[/td][td]Fla[/td][td][/td][td]Fla[/td][td][/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]Tex[/td][td=bgcolor:#DDEBF7]

3​

[/td][td=bgcolor:#DDEBF7]NY[/td][td][/td][td=bgcolor:#E2EFDA]NY[/td][td=bgcolor:#E2EFDA]1[/td][/tr]
[/table]

[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