

August 23, 2021

Hello all,
I have a table with opening hours. 1 = open, 0=closed. THat means that I have 24 values for each day, and seven days.
I would like to use the opening hours together with other data, so I want them for every hour, every day for the whole year. Please find attached .xlsx file.
The table to the left, "DateAndDay" is where I want the opening information (1 or 0).
The opening information I have put in two different tables, but I can't figure out how to lookup the correct value for every row since it depends on both which day and hours that is being used...
Thank you for any help!

VIP

Trusted Members

December 7, 2016


VIP

Trusted Members

December 7, 2016

Hello,
If you check the file I uploaded you will see examples of three different methods.
INDEX and MATCH, below formula is in Swedish.
=INDEX(Vertical[Opened];PASSA(1;(Vertical[Weekday]=[@Day])*(Vertical[Hours]=[@[Tid (UTC)]]);0);1)
XLOOKUP, below formula is in Swedish.
=XLETAUPP([@Day]&[@[Tid (UTC)]];Vertical[Weekday]&Vertical[Hours];Vertical[Opened])
SUMPRODUCT, below formula is in Swedish.
=PRODUKTSUMMA((Vertical[Opened])*(Vertical[Weekday]=[@Day])*(Vertical[Hours]=[@[Tid (UTC)]]))
/Anders

Answers Post
1 Guest(s)
