Find exact name across multiple tabs & combine with data from the most recent tab?

[Sunshine8790]

I don't know if this is even do-able, but I figure someone here will have an answer either way.

I have a spreadsheet with a main tab I'm working on (this is the tab I will want formulas in) that looks something like this:

Header	Name	Header	Header	Header	Reason
Irrelevant	Jane Doe	Irrelevant	Irrelevant	Irrelevant	FORMULA HERE
Irrelevant	John Doe	Irrelevant	Irrelevant	Irrelevant	FORMULA HERE

I have another tab that is a template that gets duplicated every week and the duplicate is simply re-named with a date (Ex: "Week End 3.30.24", "Week End 4.6.25", etc).
Looks something like this:

Columns B, G, J, O, R, W, Z, and AE are all drop-down lists.
B, J, R, and Z are lists based on Column B of the main tab.
G, O, W, and AE I just manually created in Data validation with a handful of options to choose from.

I need a formula in my "Reason" column on my main tab that searches for the name in Column B on my main tab within all these dated tabs in columns: B, J, R, and Z. (Which are all drop-down lists based off of Column B on the main tab)
IF (and only if) the name is found in any of the tabs, I need the formula to return the "Action taken" that corresponds with the name.

For example:
If "Jane Doe" is found anywhere on any of the dated tabs in columns J and/or R, I need the formula to show me the "Action Taken" in columns O and/or W of the most recent dated tab PLUS the data in I1 and/or Q1. If nothing, then show nothing. If not found in any of the tabs, also show nothing.

I hope that makes sense.