TEXTJOIN with Multiple Conditions

[Jer2224]

Hi all,

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.

 

[steve the fish]

Isnt that exactly what it does?? It produces the same results as your example.