INDEX MATCH help with dates and Fire ext number

[MedicChris]

Have been trying different solutions without avail:
Sheet 1
importing fire extinguisher inspections - Each fire Ext will get inspected once a month

Sheet 2 used for tracking purposes
Bring over date inspected then will do some conditional formatting based on info
The date inspected is a range ie.. 1/1/2022 - 1/31/2022 and as long as it falls in that range for that month it is good

Formulas i have tried (cell #'s don't match example)
=index(c:c,match(1,(c:c>=1/1/2022)*(c:c<=1/31/2022),0))
Vlookup(A4,Sheet1!B:C,2,0) - this one returned the first value only based upon the fire ext #
Started using index with maxifs but got frustrated

Any help would be greatly appreciated
you can type answer to example and i will adjust cell numbers accordingly

 

[etaf]

maybe
=MAXIFS(B2:B14,A2:A14,D2,B2:B14,">="&E1,B2:B14,"<="&EOMONTH(E1,0))

Not sure what version of excel you have

You can format the dates in the results table header to show Jan-21

Also need to add the Sheet! reference

Works of the Fire Numbers are numeric , not if text - would need to rethink that

=IF(MAXIFS($B$2:$B$14,$A$2:$A$14,$D2,$B$2:$B$14,">="&E$1,$B$2:$B$14,"<="&EOMONTH(E$1,0))=0,"",MAXIFS($B$2:$B$14,$A$2:$A$14,$D2,$B$2:$B$14,">="&E$1,$B$2:$B$14,"<="&EOMONTH(E$1,0)))

Book1
	A	B	C	D	E	F	G
1					1/1/21	2/1/21	3/1/21
2	1	1/16/21		1	1/16/21
3	2	2/24/21		2	1/1/21	2/26/21	3/8/21
4	1			3	1/12/21	2/18/21	3/24/21
5	1
6	1
7	2	1/1/21
8	2	4/1/21
9	2	2/26/21
10	2	3/8/21
11	3	1/12/21
12	3	2/18/21
13	3	3/24/21
14	3
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=IF(MAXIFS($B$2:$B$14,$A$2:$A$14,$D2,$B$2:$B$14,">="&E$1,$B$2:$B$14,"<="&EOMONTH(E$1,0))=0,"",MAXIFS($B$2:$B$14,$A$2:$A$14,$D2,$B$2:$B$14,">="&E$1,$B$2:$B$14,"<="&EOMONTH(E$1,0)))

changed format - and added NO Date , if no date found

Book1
	A	B	C	D	E	F	G	H
1	Fire	Inspect		Fire	Jan-21	Feb-21	Mar-21
2	1	1/16/21		1	1/16/21	NO DATE	NO DATE
3	2	2/24/21		2	1/1/21	2/26/21	3/8/21
4	1			3	1/12/21	2/18/21	3/24/21
5	1
6	1
7	2	1/1/21
8	2	4/1/21
9	2	2/26/21
10	2	3/8/21
11	3	1/12/21
12	3	2/18/21
13	3	3/24/21
14	3
15
Sheet1

Cell Formulas
Range	Formula
E2:G4	E2	=IF(MAXIFS($B$2:$B$14,$A$2:$A$14,$D2,$B$2:$B$14,">="&E$1,$B$2:$B$14,"<="&EOMONTH(E$1,0))=0,"NO DATE",MAXIFS($B$2:$B$14,$A$2:$A$14,$D2,$B$2:$B$14,">="&E$1,$B$2:$B$14,"<="&EOMONTH(E$1,0)))

EDIT - maybe it will work with TEXT fire ext number

Book1
	A	B	C	D	E	F	G
1	Fire	Inspect		Fire	Jan-21	Feb-21	Mar-21
2	a2	1/16/21		a2	1/16/21	NO DATE	NO DATE
3	2	2/24/21		2	1/12/21	2/26/21	3/8/21
4	1			3	1/12/21	2/18/21	3/24/21
5	1			x123a	NO DATE	2/4/21	NO DATE
6	1
7	2	1/12/21
8	x123a	2/4/21
9	2	2/26/21
10	2	3/8/21
11	3	1/12/21
12	3	2/18/21
13	3	3/24/21
14	3
Sheet1

Cell Formulas
Range	Formula
E2:G5	E2	=IF(MAXIFS($B$2:$B$14,$A$2:$A$14,$D2,$B$2:$B$14,">="&E$1,$B$2:$B$14,"<="&EOMONTH(E$1,0))=0,"NO DATE",MAXIFS($B$2:$B$14,$A$2:$A$14,$D2,$B$2:$B$14,">="&E$1,$B$2:$B$14,"<="&EOMONTH(E$1,0)))