How can I retrieve multiple values using vlookup and then transform all those values into columns next to each other?

CodePudding user response：

If you use Office 365 you can use the solution provided by pgSystemTester:

=TRANSPOSE(FILTER(B4:B9,A4:A9=C2))

Where C2 contains the search value, A4:A9 contains the values to match the search value, B4:B9 contains the result values.

If an Excel version prior to O365 is used, this could be done by: =IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($A$4:$A$9)/($A$4:$A$9=$C$2),COLUMN(A:A))),"") This indexes column B with the row numbers where a match is found on the search value C2 in A4:A9. The smallest row number matching is indexed and when copied to the right the 2nd smallest, etc.

Note 1: the INDEX part of the function uses whole column reference.

Note 2: Only in the COLUMN part of the function the $ is omitted in the range reference. This will result in changing the range reference to the right when copied to the right.

CodePudding user response：

You can use the filter function combined with transpose to accomplish what you want.

Here's