Search row for 4 criteria - return row if all criteria is met

[Doodle24]

Hello All,

I have tried to formulate something that searches through rows to meet 4 criterias. If all criteria is met I need the row number or the entire row returned, if not continue the search. Half of my criteria is straight forward BLUE=BLUE, GREEN=GREEN while the other two needs to be within a range. For example, 1 day +- from a specific date in a cell or +-500 from a specific number in a cell. I usually use if loops, vlookup and index/match but it seems I have to get into VBA with this.. Any suggestions? Thanks so much for any ideas on how to tackle this!

 

[Doodle24]

Will your datasets always pair so neatly?

I'd start the analysis along these lines:

	A	B	C	D	E	F	G	H	I	J	K	L	M
1									Possible
2		Cost	ID	Date	Length	Colour1	Colour2	Location	Matches?
3		695	1234	13 May 2022	6820	Green	Violet	NY	ABCD	EFGH
4		Blah	Blah	Blah	Blah	Blah	Blah	Blah	n/a
5
6												Possible
7		MinDate	Date	MaxDate	MinL	L	MaxL	Colour1	Colour2	ID	Name	Matches?
8		1 May 2022		31 May 2022				Green	Violet	ABCD		1234
9		Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	Bluh	n/a
10		5 May 2022		17 May 2022				Green	Violet	EFGH		1234
Sheet1

Cell Formulas
Range	Formula
I3:J3,I4	I3	=TRANSPOSE(FILTER(J$8:J$10,(H$8:H$10=F3)*(I$8:I$10=G3)*(B$8:B$10<=D3)*(D$8:D$10>=D3),"n/a"))
L8:L10	L8	=TRANSPOSE(FILTER(C$3:C$4,(F$3:F$4=H8)*(G$3:G$4=I8)*(D$3:D$4>=B8)*(D$3:D$4<=D8),"n/a"))
Dynamic array formulas.

Thanks Steven, this works!! I appreciate you taking time and looking into the problem. I guess I have to get more familiar with the Filter function - its a great tool. I would wish to have something automated but this is a good solution too.