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

Barque

New Member
Joined
Nov 25, 2020
Messages
13
Office Version
  1. 365
I have used the formulas below to get the start and end of the week # in the referenced year in column H :

1617989249144.png

1617989278957.png


Full worksheet is

1617989696074.png


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.

1617989876620.png


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
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 25, 2020
Messages
13
Office Version
  1. 365
"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
Joined
Dec 30, 2008
Messages
12,640
Office Version
  1. 365
Platform
  1. Windows
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 :oops:
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)))),"")
 
Solution

Barque

New Member
Joined
Nov 25, 2020
Messages
13
Office Version
  1. 365
Awesome, thank you. Appreciate the help and the lesson.
 

Watch MrExcel Video

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top