Formula like a Vlookup but to bring back multiple results

[dc007c0231]

Hi all,

I need to bring back multiple results from a list based on a specific text, then I need to populate cells across columns (i.e. on the same row). Please see example for exactly what I am after. I can only have one row per person which is why the results need to go into different columns.

Any help will be appreciated.

Thanks
James

[WideBoyDixon]

Try this in C2:

Please Login or Register  to view this content.

WBD

[dc007c0231]

Hi WBD,

If you've seen my reply please ignore as it was wrong. That formula does work. Thank you so much. I just need to try and understand it now!!

Thanks
James

[wdt270]

Good Morning ,

What are the step by step process for completing a Vlookup formula in EXCEL.

[WideBoyDixon]

Walkthrough of:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Just return blank if we run into difficulties!

Please Login or Register  to view this content.

Filter the table in K:M and only return the rows where column K is the same as A2. Assign this to a variable called f.

Please Login or Register  to view this content.

Generate a sequence of numbers with twice the number of entries as the number of rows in f. Start at 2. In our case, this will generate a sequence {2, 3, 4, 5, 6, 7}. Assign this to a variable called s.

Please Login or Register  to view this content.

Alternate between columns 2 and 3 of f and work down the table to return all columns. This is essentially returning INDEX(f, 1, 2), INDEX(f, 1, 3), INDEX(f, 2, 2), INDEX(f, 2, 3), INDEX(f, 3, 2), INDEX(f, 3, 3) - i.e. columns L and M from the table. Because this returns a result in rows, use TRANSPOSE to change it to a column result.

Hope that helps.

WBD

[wk9128]

ANS. POST#1

Cell C2 formula , drag down and across

Please Login or Register  to view this content.

ANS. POST#3

Cell C2 formula , drag down and across

Please Login or Register  to view this content.

[dc007c0231]

Hi wk9128,

Thank you for your formula, that also works very well. Much appreciated.

Regards
James

[wk9128]

@dc007c0231 You're Welcome. Glad to help . Thank You for the feedback and rep.