Lookup Over Multiple Sheets

[alexanders]

Hello I have several sheets like this:

CIP Workbook 2022.xlsx
	A	B	C	D	E	F	G	H	I
1							03/01/2022
2							Week 1
3		Plant Item	Frequency	Date of Last Event	Days Since Event	Days Until Action Req'd	Clean Type	Name	Date
4	overdue	Pasteuriser Sample Tap Membrane	Weekly	01/11/2021	20	13 days overdue!	Hot Caustic	AB	01/11/2021
5		Post passi Media Sample (sterile sample)	Weekly	17/11/2021	4	3	Cold Caustic	AC	17/11/2021
6		Soak baths	Weekly	18/11/2021	3	4	Hot Rinse	AA	18/11/2021
7		Pasteuriser strainers	Weekly	19/11/2021	2	5	Cold Rinse	AC	19/11/2021
8	overdue	Juice Buffer Tank	Weekly	01/11/2021	20	13 days overdue!	Hot Caustic	AA	01/11/2021
9		Media Blender and Passi Main (New)	Weekly	17/11/2021	4	3	Hot Caustic	AC	17/11/2021
10		Media Blender and Passi Main (Old)	Weekly	18/11/2021	3	4	Cold Caustic	AA	18/11/2021
11		FFRT Lees Main	Weekly	19/11/2021	2	5	Hot Rinse	AC	19/11/2021
12	overdue	Transfer Main 1A	Weekly	01/11/2021	20	13 days overdue!	Cold Rinse	AA	01/11/2021
13		Transfer Main 2A	Weekly	16/11/2021	5	2	Hot Caustic	AC	16/11/2021
14		Transfer Main 1B	Weekly	17/11/2021	4	3	Hot Caustic	AA	17/11/2021
Sheet2

Cell Formulas
Range	Formula
D4:D14	D4	=IF(MAX(G4:FF4)=0/1/1900,"NOT DONE",MAX(G4:FF4))
E4:E14	E4	=IF(MAX(G4:FF4)=0/1/1900,"",(TODAY()-MAX(G4:I4)))
F4:F14	F4	=IF(E4="","",IF(E4<=7,7-E4,IF(AND(E4>7,E4<=14),"Action within "&14-E4&" days",IF(E4>14,E4-7&" days overdue!"))))
A4:A14	A4	=IFERROR(IF(SEARCH("overdue",F4),"overdue"),"")

Cells with Data Validation
Cell	Allow	Criteria
G4:G25	List	=Data!$A$2:$A$14
H4:H25	List	=Data!$B$2:$B$35
I4:I25	Custom	=I4=TODAY()

And I would like a status sheet able to automatically locate the overdue items and display them something like this:

CIP Workbook 2022.xlsx
	A	B	C	D	E
1	Overdue Cleans Status Report
2	Plant Item	Frequency	Date of Last Event	Days since last event	Days until next action required
3	Pasteuriser Sample Tap Membrane	Weekly	01/11/2021	20	13 days overdue!
4	Juice Buffer Tank	Weekly	01/11/2021	20	13 days overdue!
5	Transfer Main 1A	Weekly	01/11/2021	20	13 days overdue!
6
7
Status

But whenever I try it picks up the first overdue item but then just repeats it over and over

Is there a way to lookup over multiple sheets and display the items which are overdue please?

 

[alexanders]

Well I have looked at this for some time but have no satisfaction ?‍? I have this formula which works good, but for just one sheet

{=IFERROR(INDEX(Sheet2!$B$2:$B$9,SMALL(IF($C$26=Sheet2!$A$2:$A$9,ROW(Sheet2!$A$2:$A$9)- MIN(ROW(Sheet2!$A$2:$A$9))+1,""), ROW()-2)),"")}

And I have this formula which works for multiple sheets but only if search term is different
{=VLOOKUP($C26,INDIRECT("'"&INDEX(lookup,MATCH(1,--(COUNTIF(INDIRECT("'"&lookup&"'!$A$1:$c$40"),$C26)>0),0))&"'!$A$1:$c$40"),2,FALSE)}

Can you just tell me if it's possible to have a formula like this first one that searches over multiple sheets please?