Using Excel for Office 365 MSI 16.0.11 x64 version 1902.

I have a scenario where I think TEXTJOIN would work, but I can't figure out how to concatenate several IF statements together.

I have:

- Column A (id) with hundreds of row entries, simple numbers 1-.....

- Column B (category) with a data validation list of 10 or so entries in every cell.

- Column C (review) with free text fields.

- Column D (related).

What I am trying to solve is a formula for column D which first looks for specific free text in column C (review), and if it finds "review" then looks up and returns all the "id" entries from column A for the specific list entry in B, minus it's own. When no entries are found, n/a is returned.

Example:

1 apples review 3, 6

2 oranges old n/a

3 apples review 1, 6

4 apples old n/a

5 oranges new n/a

6 apples review 1, 3

7 bananas new n/a

I have tried several combinations of IF, SUMIFS, and other functions but am not having any luck. I also don't know VBA so don't know how to code that yet.

Would be grateful for any pointers.

Many thanks.

Jer2224.