Help with IF statement formula; working from specific dataset depending on the day of the week

EvoUK

New Member
Joined
Jul 1, 2022
Messages
20
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,

I hope you are all doing well.

I have an IF formula (shown below) which has been working well but I wanted to add another level of complexity to it.
The current formula looks up 'time' from a dataset of different timings and calculates/displays the 'time' from the table which is the next event to the current time.
Currently, there are 3 different datasets which the formula works from: Weekday, Saturday and Sunday.
Depending on the current day, the formula selects the relevant dataset to use (either weekday or Sat or Sun sets).

Excel Formula:
Excel Formula:
=IF(WEEKDAY(E3,2)=6, XLOOKUP($B$1+1/86400,[B]data_pad2occ1![/B]$I$4:$I$76,[B]data_pad2occ1![/B]$I$4:$I$76,"not found",1), IF(WEEKDAY(E3,2)=7, XLOOKUP($B$1+1/86400,data_pad2occ1!$O$4:$O$76,data_pad2occ1!$O$4:$O$76,"not found",1), XLOOKUP($B$1+1/86400,data_pad2occ1!$C$4:$C$76,data_pad2occ1!$C$4:$C$76,"not found",1)))

Current time: B1
Current day: E3

Primary datasets locations:
(All are located on sheet data_pad2occ1)
$C$4:$C$76 Weekday
$I$4:$I$76 Saturday
O$4:$O$76 Sunday


The issue I have is:
I have supplementary datasets for each day of the week (the data within these sets DO change every week).
The primary datasets (Weekday, Sat and Sun) as above doesn't change and their current setup is fine. I am hoping it is possible to modify the formula to use both the primary dataset (as it currently does) BUT also include data from the supplementary datasets depending on the day of the week?

E.g. If today is Monday, the formula will work on the data from the primary weekday dataset (as it currently does):

data_pad2occ1!$C$4:$C$76

BUT ALSO include data from the relevant supplementary set: e.g. tab_B!$A$1:$C$76 (assuming this is the location for the dataset for Monday).

So when the IF formula calculates the next time event, it will be based on both the Weekday dataset and the Supplementary Monday dataset. And the same for if today was Saturday, it would use the primary Saturday dataset + the supplementary Saturday dataset.

Assuming tab_B contains data for the supplementary sets

A1:76 Monday
B1:76 Tuesday
C1:76 Wednesday

etc going up to Sunday.


I am hoping this is the simplest way to solve this issue? How do I modify the formula to achieve this function?
Any alternative solutions would also be appreciated.

Thanks in advance
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I think, for the forum to really give you the best assistance you need to post an xl2bb addin mini worksheet for both the main sheet and the supplemental sheet.

But, to answer your question, since you have 365, you can use the VSTACK function which puts two arrays together.

edited: Originally had HStaCK and meant to have VSTACK.


mr excel questions 22.xlsm
ABCDE
5252
6363
7474
85
96
107
EvoUK
Cell Formulas
RangeFormula
E5:E10E5=VSTACK(A5:A7,C5:C7)
Dynamic array formulas.
 
Upvote 0
I think, for the forum to really give you the best assistance you need to post an xl2bb addin mini worksheet for both the main sheet and the supplemental sheet.

But, to answer your question, since you have 365, you can use the VSTACK function which puts two arrays together.

edited: Originally had HStaCK and meant to have VSTACK.


mr excel questions 22.xlsm
ABCDE
5252
6363
7474
85
96
107
EvoUK
Cell Formulas
RangeFormula
E5:E10E5=VSTACK(A5:A7,C5:C7)
Dynamic array formulas.
Hi,

Thank you for your reply.

Is it possible to use the VSTACK function within XLOOKUP? Could you provide an example of how I would successfully implement this into the formula below please?

Assuming the first dataset is A1:A3 and the second is B1:B3.

=IF(WEEKDAY(E3,2)=6,
XLOOKUP($B$1+1/86400,data_pad2occ1!$A$1:$A$3,data_pad2occ1!$A$1:$A$3,"not found",1),
 
Upvote 0
@EvoUK Here you go:
mr excel questions 22.xlsm
ABCDEF
52a5d2a
63b6e3b
74c7v4c
85d
96e
107v
11
124c
EvoUK
Cell Formulas
RangeFormula
E5:F10E5=VSTACK(A5:B7,C5:D7)
E12E12=VLOOKUP(D12,VSTACK(A5:B7,C5:D7),2,0)
Dynamic array formulas.
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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
Back
Top