Automatically perform formula repeatedly on different cells within another formula

[WorldTraveler]

Hello,

In the sheet titled Data have a list of books organized by year. This also includes the author. Often there is more than one author per book because they often collaborated. Relevant to my question is the fact that years are in column A and authors in column D.


Now, each author was part of 1 or more organizations. Please ignore column B above because it's not entirely accurate. Instead to link authors to organizations I have a list of authors under each organization they belonged to in Sheet 4. For example, authors who were in BR are listed in Sheet 4, column E, rows 5-89 like so:

I realize this is not the greatest setup, but what I'm trying to do is link the organization with the years in which their members published a book, repeating years if there was more than one publication so that later I can create a bar graph for each organization with # of publications per year.

So for example, I'd want to end up with a list like this for the BR:

1995

1995

1996

1997

1997

1997

1997

1998

1998

I've developed a clunky formula that gives me the years of publications by any authors listed in cells E5 to E9 in Sheet 4:

=FILTER(Data!A:A,ISNUMBER(SEARCH(SHEET4!E5,Data!D:D))+ISNUMBER(SEARCH(SHEET4!E6,Data!D:D))+ISNUMBER(SEARCH(SHEET4!E7,Data!D:D))+ISNUMBER(SEARCH(SHEET4!E8,Data!D:D))+ISNUMBER(SEARCH(SHEET4!E9,Data!D:D))

However, typing out ISNUMBER(SEARCH(SHEET4![cell],Data!D:D)) 84 times for 84 cells is not fun. Is there a way to automate this so that Excel will automatically check all authors listed in cells E5 to E89 individually before conducting the FILTER formula?

=FILTER(Data!A:A,ISNUMBER(SEARCH(SHEET4!E5:E89,Data!D:D)) does not work because this is asking Excel to see whether all of the authors' names are listed for the same book, which of course is never the case.

Even better would be a formula that when it sees this:

1981 Book A Author A, Author B, Author C

and

Organization 1: Author A

Organization 2: Author B, Author C

does not count the text twice for organization 2, but that goes well beyond my capabilities.

This is on Excel for Windows 11 in Microsoft 365 office for enterprise. I think that's version 2502?

Many thanks!

 

[saboh12617]

No problem, i'm glad you made it work.

Buona giornata ~