Index Match with time range criteria?

[sleeplab]

The Problem
So the raw data I'm using looks like this:

Date	Time	Activity	Code
8/31/2017 <tbody> </tbody>	16:05	10	7
8/31/2017	17:13	5	1
9/1/2017	15:00	1	4

<tbody>
</tbody>

And I'm trying to format it to look like this:

Date	Activity_15:00	Code_15:00	Activity_16:00	Code_16:00	Activity_17:00	Code_17:00
8/31/2017			10	7	5	1
9/1/2017	1	4

<tbody>
</tbody>

Criteria: Timestamp on the code must be within 15 minutes of the hour

So I figured I'd use an index match to compare the time stamp to an independent table with times (ie. 15:45/16:15). However I'm running into problems trying to create an array, and being able to select both the activity value, and code value.

Am I using the correct function with a index match, or vlookup?

Thanks.

 

[Oscar Cronquist]

It is hard to build a formula that uses the values in the first row. If you can rearrange your values in the first row?

Date Activity_15:00 Code_15:00 Activity_16:00 Code_16:00 Activity_17:00 Code_17:00

to something like this:

Activity_15:00 Activity_16:00 Activity_17:00