Filter or other function - Return values based on first and last criteria

[Barque]

I have used the formulas below to get the start and end of the week # in the referenced year in column H :

Full worksheet is

I need to retrieve the 1st value of the week for the"6AM" column C highlighted in GREEN and the last value of the week for the "6PM" column E also in GREEN

I have populated columns M and O using MAXIFS and MINIFS formulas as suggested which have worked great.

My data only uses a M - F business calendar. and because of Holidays, I cannot figure out the correct function/formula to retrieve the correct data. For example, we had a holiday on 4/2/2021 - so the correct value for "6PM" for the week of 3/28/2021 - 4/3/2021 would be the value in cell E6, opposed to whatever value is on a Friday with no Holidays.

I think I need two filters. First, filter the dates if they are within the week referenced in columns K and L. Second, retrieve the 1st value of the week for the 6AM column of C, and last value of the week for column E.

I have tried using IFERROR function with index/match and filter but cannot get things to work. Any suggestions are much appreciated.

 

[jasonb75]

I have populated columns M and O using MAXIFS and MINIFS formulas as suggested which have worked great.

Are they?

Those functions would only return the lowest and highest values for the week, not the earliest and latest. They would work with your screen capture because the fictional data used increases on a daily basis, but with random data in columns B:E they would be incorrect.

Do you have a list of holidays anywhere in the sheet that could be used as a reference?

 

[jasonb75]

Taking a different approach, this appears to work as required.

autoclose test.xlsm
	M	N	O	P
1	12AM	6AM	12PM	6AM
2	60	70	100	80
3	57	67	97	77
4	50	60	90	70
Sheet13

Cell Formulas
Range	Formula
M2:P4	M2	=IFERROR(LOOKUP(1E+100,(INDEX(B$2:B$17,XMATCH(SEQUENCE(7,,$K2),$A$2:$A$17,0,-1)))),"")

 

[Barque]

"Those functions would only return the lowest and highest values for the week, not the earliest and latest.", Correct, That is what I need for those columns.

It does work even when the data is not steadily increasing or decreasing. The MINIFS and MAXIFS correctly finds the MIN and MAX values for the referenced week in the range for those columns.

For the referenced week, I need the first value from Column C, and the last value from Column E. The recent Holiday on 4/2/2021 gave me errors, I needed my formula to return the last day of that week with a value - which is cell E6.

I tried your formula for N which references Column C, 6AM, and it returned 70. I would like the formula to return 68, the value in cell C4. The logic is this is the first value for column C in the referenced weekly range of 4/4/2021 - 4/10/2021. And for Column P, I need it to return the last value in Column E with a value - which is E2.

=IFERROR(LOOKUP(1E+100,(INDEX(C$2:C$17,XMATCH(SEQUENCE(7,,$K2),$A$2:$A$17,0,-1)))),"")

 

[jasonb75]

The formula is correct for column P with the relative references adjusted, although it wasn't quite doing what I originally had in mind, it still works correctly for that part. I know what I was thinking, but in hindsight, I have no idea why I was thinking it

=IFERROR(LOOKUP(1E+100,(INDEX(E$2:E$17,XMATCH(SEQUENCE(7,,$K2),$A$2:$A$17,0,-1)))),"")

then this variation for column N

=IFERROR(LOOKUP(1E+100,(INDEX(C$2:C$17,XMATCH($L2-SEQUENCE(7,,0),$A$2:$A$17,0,-1)))),"")

 

[Barque]

Awesome, thank you. Appreciate the help and the lesson.