Something like COUNTIF, but it shows an actual lookup value?

This might be too complex to do in an Excel formula, but I'd like to try.

Imagine I have this table:

A	1
A	2	1
A	3	1,2
B	1
A	4	1,2,3
B	2	1
B	3	1,2
C	1
A	5	1,2,3,4
C	2	1

I want to write a formula that calculates column C in that table. Column C basically: grabs the value of Column A in its own row, looks for all the times that same value has been in Column A so far, and then lists the contents of Column B for every Column with the same value in Column A.

So, for row 3, it finds all the instances where Column A is "A", looks up the values of Column B (1, 2), and lists them.

Is that possible? Right now I've got it simply counting the appearances so far, but listing them has eluded me.

I do! What function in Excel would return multiple cells to be concatenated?

Do you mean TextJoin?

In your case, you could do something like...

Formula:

Please Login or Register  to view this content.

Confirmed as Array (CTRL + SHIFT + ENTER).

See attached sample.

NOTE: I'd recommend that you update your profile to include "MS-Off Ver" info