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

#### Barque

##### New Member
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.

### Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

#### jasonb75

##### Well-known Member
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

##### Well-known Member
Taking a different approach, this appears to work as required.
autoclose test.xlsm
MNOP
112AM6AM12PM6AM
2607010080
357679777
450609070
Sheet13
Cell Formulas
RangeFormula
M2:P4M2=IFERROR(LOOKUP(1E+100,(INDEX(B\$2:B\$17,XMATCH(SEQUENCE(7,,\$K2),\$A\$2:\$A\$17,0,-1)))),"")

#### Barque

##### New Member
"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

##### Well-known Member
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
Excel Formula:
``=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
Excel Formula:
``=IFERROR(LOOKUP(1E+100,(INDEX(C\$2:C\$17,XMATCH(\$L2-SEQUENCE(7,,0),\$A\$2:\$A\$17,0,-1)))),"")``

#### Barque

##### New Member
Awesome, thank you. Appreciate the help and the lesson.

Replies
6
Views
148
Replies
45
Views
725
Replies
7
Views
342
Replies
3
Views
427
Replies
4
Views
64

1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back