Recreating ARRAY_CONSTRAIN(ARRAYFORMULA in excel

Question

I have a formula in a Google Sheet that we are transitioning into excel. I'm not sure if this is the most effective way to accomplish what we'd like to do. 

We have a chart of dates on one tab (Date Tables), and I'm trying to return the soonest date following a reference date in my main sheet.

Our Date Tables Sheet has includes the first date of each pay period ongoing.  Based on the date in cell O2, I need the formula to return the soonest date from the list FOLLOWING their 6 Month Anniversary

Date Tables Sheet has the Pay Period Start Dates in Column B. In this instance I would need it to return "9/13/2021"

The formula we were using in Google Sheets is =ARRAY_CONSTRAIN(ARRAYFORMULA(IF(E2<>"",MIN(IF('Date Tables'!$B:$B>=(E2+182),'Date Tables'!$B:$B)),"")), 1, 1)

The formula was also calculating the 6 months which we no longer need it to do.

Answer 1

I think I need something that determines what the "next" date in the chart is. So, if the date in O2 is 9/6, I need something that references the table and identifies that 09/06 is greater than 08/30 but less than 09/13 and then know to return 09/13

It does that only, please see screenshot below

if you are not using a table, you can use

=IFERROR(INDEX(Sheet1!$B$3:$B$21,MATCH(O2,Sheet1!$B$3:$B$21,1)+1),"No Date found")

Answer 2

say your date table is converted to a table (select the cells and press ctrl+t) named "DateTable"

you can use the following formula in cell P2 and copy down

=IFERROR(INDEX(DateTable[1st day of pay period],MATCH(O2,DateTable[1st day of pay period],1)+1),"No Date found")

Answer 3

This didn't work - it came back as "No Date Found".

I think I need something that determines what the "next" date in the chart is. So, if the date in O2 is 9/6, I need something that references the table and identifies that 09/06 is greater than 08/30 but less than 09/13 and then know to return 09/13

Answer 4

Hi,

In cell P2, enter this formula

=XLOOKUP(O2,$B$2:$B$20,$B$2:$B$20,"",1)

Hope this helps.

Answer 5

This worked! Thank you so much.