Log In
Register
Home
August 23, 2021
Offline
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!
See Answer
VIP
Trusted Members
December 7, 2016
Offline
Hej Mattias!
The time values in table Vertical is formatted as text, while in table DateAndDay they were correctly as numbers.
In attached copy of your sample file I have corrected that, so now you can do a lookup with for example INDEX and MATCH or XLOOKUP.
Br,
Anders
August 23, 2021
Offline
Tack Anders!
I just made an example to explain what I want, so I hadn't spent any time on the formatting.
What I need help with is how to write the formulas using INDEX, MATCH or XLOOKUP as you describe..
Trevlig helg!
Mattias
VIP
Trusted Members
December 7, 2016
Offline
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)
