Excel: Text-Based Multi-Lookup Formula

Question

I have a table describing a basic two-way relationship system.

For simplicity, I'll just use animals as an example.
(My actual spreadsheet is needlessly complicated, and wouldn't be helpful.)

I list animals with a unique text name (identifier).
Then in another column, I list the animals that are related the animal in the ID column.

And finally, I need a column which calculates the relationships in reverse.

In order to accomplish this, I'll need to search the entire second column, and produce a list of Animal IDs that contain a certain word in column 2.

I know this might be a stretch for an Excel formula, but it would be hugely beneficial if I could keep this out of PowerQuery or VBA for now.
(This table won't be very big, maybe 100 rows at most.)

If you're on Excel 2016 or Office 365, you should be able to use [TEXTJOIN](https://support.office.com/en-gb/article/textjoin-function-357b449a-ec91-49d0-80c3-0e8fc845691c) for this as an array formula, like so: `=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(A2,$B$2:$B$7)),$A$2:$A$7),"")` Put that formula in cell C2 (and don't forget to confirm it with Ctrl+Shift+Enter to make it an array formula) and then copy down. If you're on an earlier version of Excel, this will require that the results be in separate cells, or you'll need VBA. — tigeravatar, Apr 02 '19 at 18:54
@tigeravatar With some tinkering, I discovered a minor error in your function. A closing parentheses was misplaced. `$A$7),"")` should have been `$A$7,""))`. I made this change, and the function works perfectly! Thanks! (You can post as an answer, and I'll accept.) — Giffyguy, Apr 02 '19 at 20:39

score 1 · Accepted Answer · edited Apr 02 '19 at 22:20

Per my comment:

If you're on Excel 2016 or Office 365, you should be able to use TEXTJOIN for this as an array formula, like so:

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH(A2,$B$2:$B$7)),$A$2:$A$7,""))

Or, if you prefer to use table notation (without the "Unique" column tags):

=TEXTJOIN(", ",TRUE,IF(ISNUMBER(SEARCH([@Animal],[Related Animal])),[Animal],""))

Put that formula in cell C2 (and don't forget to confirm it with Ctrl+Shift+Enter to make it an array formula) and then copy down. If you're on an earlier version of Excel, this will require that the results be in separate cells, or you'll need VBA.

NOTE: Placement of closing parentheses was corrected by OP Giffyguy

Excel: Text-Based Multi-Lookup Formula

1 Answers1