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

[EvoUK]

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:

=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

 

[awoohaw]

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
	A	B	C	D	E
5	2		5		2
6	3		6		3
7	4		7		4
8					5
9					6
10					7
EvoUK

Cell Formulas
Range	Formula
E5:E10	E5	=VSTACK(A5:A7,C5:C7)
Dynamic array formulas.

 

[EvoUK]

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
	A	B	C	D	E
5	2		5		2
6	3		6		3
7	4		7		4
8					5
9					6
10					7
EvoUK

Cell Formulas
Range	Formula
E5:E10	E5	=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),

 

[awoohaw]

@EvoUK Here you go:

mr excel questions 22.xlsm
	A	B	C	D	E	F
5	2	a	5	d	2	a
6	3	b	6	e	3	b
7	4	c	7	v	4	c
8					5	d
9					6	e
10					7	v
11
12				4	c
EvoUK

Cell Formulas
Range	Formula
E5:F10	E5	=VSTACK(A5:B7,C5:D7)
E12	E12	=VLOOKUP(D12,VSTACK(A5:B7,C5:D7),2,0)
Dynamic array formulas.