First (and last) occurrence of set of values in a row gives corresponding column value

[jimmychacha]

Hi there,

This is my first post, I'm a big fan of this forum, it has helped me in many a sticky situation. So want to say a huge thank you to the community here for all your generous help and hard work.

This following sticky situation however has really stumped me...

I am looking for the following formulas:

1: In Column H, it would find the first occurrence of either "1" or "2" in the range A$:G$, and give the corresponding date in Row 1 of the column where it appears.
2: In Column I, it would find the last occurrence of either "3" or "4" in the range A$:G$, and give the corresponding date in Row 1 of the column where it appears.

For example, in cell H2, the formula would find the first column in row H where "1" or "2" appears (in this case D2), and give the corresponding date in that column (in this case D1).

Thank you in advance!

 

[Cubist]

I believe I2 is wrong in the picture based on your description.

Book1
	A	B	C	D	E	F	G	H	I
1	4/22/24	4/23/24	4/24/24	4/25/24	4/26/24	4/27/24	4/28/24
2	4	4	3	2	3	4	1	4/25/24	4/27/24
3	3	1	4	2	1	4	3	4/23/24	4/28/24
4	1	2	4	1	4	2	1	4/22/24	4/26/24
Sheet1

Cell Formulas
Range	Formula
B1:G1	B1	=A1+1
H2:H4	H2	=MINIFS($A$1:$G$1,A2:G2,"<3")
I2:I4	I2	=MAXIFS($A$1:$G$1,A2:G2,">2")

 

[jimmychacha]

Well spotted, you're absolutely right of course, I2 was incorrect.

Thank you so much for the speedy response, that solution a treat! Much appreciated!