Return and concatenate values array formula

[BWellman]

Hey,

I have a data table which has an ID, segment, Name, company, and score. There are three segments, and I am looking to return all of the name, companies, and scores for a given segment.

The challenge is that the name and companies need to be concatenated into one cell, while the score can stay separate (i.e., ("Name" &"-"& "company")

I know the first part is an array / index match formula that uses a row count to get rid of blanks, I'm just not sure how to combine that with a concatenate formula.

I have included an example document for reference. I am looking to return the values into column I and J depending on the segment chosen in I2.

Regards,
B

[leelnich]

Hi B - paste this ARRAY FORMULA* in I6 and copy down:

Please Login or Register  to view this content.

...and this in J6:

Please Login or Register  to view this content.

I assumed you wanted each person to appear only once on the list. If DUPLICATES are permissible, post # 3 is a better fit.

*Always press CTRL+SHIFT+ENTER to confirm entry after pasting or editing an ARRAY FORMULA in the Formula Bar.
When copying the formula to other cells, make sure the copied cell is not part of the Paste Area.

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[Aardigspook]

An alternative:

In I6:

Please Login or Register  to view this content.

In J6:

Please Login or Register  to view this content.

Both are array formulas, entered with Ctrl-Shift-Enter.
Copy down.

Hope that helps.

[Aardigspook]

Thanks for the rep and for marking the thread as Solved.