Match if between 2 values (i.e. multiple criteria)

Teapotlid

New Member
Joined
Jan 30, 2015
Messages
14
I have this input table tracking time spent on various activities over a year (first table below). I want to show it in an output table with rows as days and time slots as columns (see 2nd table below) to get an overview of how time was spent.
I can use IndexMatchMatch in I2 for the Aug 3 type activity as the times neatly coincide with the output table.
For the Aug 4 activities I have a problem as I want to match if the time in output table row 1 is equal to/between the times in the input table columns B & C.
I've tried loads of different options & am stumped.
Surely this should be doable?
Would really appreciate help from the experienced index/matchers here.
Thanks in advance.

Input Table (c 1 yr's worth of data - not always in order)
[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Date[/TD]
[TD]From[/TD]
[TD]To[/TD]
[TD]Activity[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aug 3, 2017[/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]Meetings[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aug 4, 2017[/TD]
[TD]1:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]Thinking[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Aug 4, 2017[/TD]
[TD]5:00 PM[/TD]
[TD]8:00 PM[/TD]
[TD]Meetings[/TD]
[/TR]
</tbody>[/TABLE]

Desired Output Table (I'll use conditional formatting to show activities by color)
[TABLE="width: 700"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[TD]O[/TD]
[TD]P[/TD]
[TD]Q[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]12:00 PM[/TD]
[TD]1:00 PM[/TD]
[TD]2:00 PM[/TD]
[TD]3:00 PM[/TD]
[TD]4:00 PM[/TD]
[TD]5:00 PM[/TD]
[TD]6:00 PM[/TD]
[TD]7:00 PM[/TD]
[TD]8:00 PM[/TD]
[TD]9:00 PM[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Aug 3, 2017[/TD]
[TD]Meetings[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Aug 4, 2017[/TD]
[TD][/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Thinking[/TD]
[TD]Meetings[/TD]
[TD]Meetings[/TD]
[TD]Meetings[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

****** id="cke_pastebin" style="position: absolute; top: 218px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">[TABLE="width: 500"]
<tbody>[TR]
[TD]Aug 3, 2017[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
@Teapotlid, given your example above, place the following formula into I2:

Code:
=IFERROR(INDEX($D$2:$D$4,SUMPRODUCT(($A$2:$A$4 = $H2)*($B$2:$B$4 <= I$1)*($C$2:$C$4 > I$1)*(ROW($D$2:$D$4)))-1),"")

Select cell I2 and drag-copy the formula over to R2. Then, with I2:R2 still selected, drag-copy down to fill new rows as required.
 
Upvote 0
Thanks for this - I was clearly stuck on Match as an answer!:)

I've been trying it out with more inputs and a bigger time range. My inputs start on 1st July & I've tested out one hour band per day. I'm finding places where it doesn't work.
- 3am-4am appears in both the 3am output column and the 4am column. Same problem with 6am-7am
- 4am-5am, 7am-8am, and 11pm-12am don't show anything.
- The date seems to have no impact on whether it works.

Any ideas?
 
Upvote 0
@Teapotlid, being a formula, there's no reason that one time should work over another. I would guess that the issue is that adjustments to the formula were made incorrectly or that you've changed the ranges involved. The only way for me to know would be to see what you see. If you can post the entirety of both existing ranges with their column and row headers, I'll take a look.
 
Upvote 0
@ErikTyler I guess I've just demonstrated that I don't understand how the formula works: I can't see why it'd work sporadically because I don't get how it's working. Being dim:confused:
Not sure how to attach here: via a Dropbox link?
 
Upvote 0
Dropbox link will work. Many people who offer solutions here are not allowed to access external links due to work restrictions; but since I work from home, I sometimes will.

Busy day today. I'll check back this evening, though.
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,173
Members
453,021
Latest member
Justyna P

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