I've encountered a problem in Excel that I don't know how to approach solving.

There are two tables in the image above.

Table 1 is a list of gifts exchanged between individuals, with the giver and the receiver identified. (I'm using "gifts" and "people" to make it easier to discuss here. In reality these are pieces of equipment exchanging control signals. This table can be hundreds of rows long.)

Table 2, columns E and F, is a list of unique person-pairs (irrespective of giver and receiver). I have already written formulas to search columns A and B and return only unique pairs in E and F. Now, for each unique pair, I need to return all of the gifts exchanged between the two individuals in column G (I have manually entered what column G should contain with a working formula.

I'm not even sure where to begin with this problem. Column G3's formula should have something like the following in it:

=and(or(B3=$E$3,C3=$E$3),or(B3=$F$3,C3=$F$3)),A3,"")

But then, the cell needs to search the entire range B:B and C:C and TEXTJOIN every A:A in which the conditions are met.

I am fine using VBA for a solution (even then, I'm not sure where to begin), but would prefer not; the data will be imported into another piece of software and that software can't execute VBA code.

Can anyone point me in the right direction?

Thanks for your help.

CodePudding user response：

Use FILTER

=TEXTJOIN(", ",TRUE,FILTER(A:A,((B:B=$E$3) (C:C=$E$3))*((B:B=$F$3) (C:C=$F$3)),""))