PowerPivot LOOKUPVALUE With Conditions

[Qwertyu]

Firstly, i'm not sure that LOOKUPVALUE is the right function to be using for this scenario. So please let me know if it isn't and I will change the title.

I have two tables as shown in the image below.

What I want to do is get Table 3 to look up table 2 and show what exercise was being done during the relevant minutes.

For example Participant A on the 01/04/2020 did 3 runs (Run 1, 2, 3) each lasting 5 mins. So in table 3 Min01,02,03,04,05 would have Run 1 in the "new" column and Min06,07,08,09,10 would have Run 2.

I have added in the Total exercise Y/N column as the 'Total exercise' will dictate the start and end of exercise for each participant in that session / on that day.

Any help / questions would be appreciated. Thanks.

 

[Qwertyu]

I know the columns on the left hand table (Table 2) aren't very clear so please see above image.

 

[Matt Allington]

I suspect you need to push a virtual filter(s) from one table to the other. I wrote an article here How Many Working Days Has An Employee Been Off Work? - P3

it is a different topic but the same principle. You need to write a filter (FILTER function) that detects the time from/to in one table and uses that to filter the other. Hope this helps.