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.
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.