VLOOKUP (or like) on a range?

[ronald.gravendeel]

Hello,

I'm looking for a way to get a VLOOKUP (or equivalent effect) on a value that falls within a range, in stead of a predefined value.

Say for instance, I'm timing the test of 3 students, Jim, Bob & Ann. For each of the students, I note when they begin a new question. the resulting data sheet I get is:
<table BORDER=1><TR><TD>Jim</TD><TD>Bob</TD><TD>Ann</TD></TR>
<TR><TD> 9:05 1 </TD><TD>9:03 1 </TD><TD>9:02 1</TD></TR>
<TR><TD>9:15 2</TD><TD>9:16 2</TD><TD>9:09 2</TD></TR>
<TR><TD> 9:30 3</TD><TD>9:31 3</TD><TD>9:17 3</TD></TR>
<TR><TD> 9:46 4</TD><TD>9:49 4</TD><TD>9:29 4</TD></TABLE>

I want to make a table that indicates for each agent which question they began with a 5 minute period. So that table would look like this:
<table BORDER=1><TR><TD></TD><TD> Jim </TD><TD>Bob</TD><TD>Ann</TD></TR>
<TR><TD>9:00 - 9:05</TD><TD>1</TD><TD>1</TD><TD> 1</TD></TR>
<TR><TD>9:05 - 9:10</TD><TD></TD><TD></TD><TD>2</TD></TR>
<TR><TD>9:10 - 9:15</TD><TD>2</TD><TD></TD></TR>
<TR><TD>9:15 - 9:20</TD><TD></TD><TD>2</TD><TD>3</TD></TR>
<TR><TD>9:20 - 9:25</TD><TD></TD><TD></TD></TR>
<TR><TD>9:25 - 9:30</TD><TD>3</TD><TD></TD><TD> 4</TD></TR>
<TR><TD>9:30 - 9:35</TD><TD></TD><TD>3</TD><TD></TD></TR>
<TR><TD>9:35 - 9:40</TD><TD></TD><TD></TD><TD></TD></TR>
<TR><TD>9:40 - 9:45</TD><TD></TD><TD></TD><TD></TD></TR>
<TR><TD>9:45 - 9:50</TD><TD>4</TD><TD>4</TD><TD></TD></TABLE>
How can I get VLOOKUP (or another function) to recognize that a given time lies within the predefined time-range?

Thanks in advance</p>

 

[shippey121]

this can be done but will require some extra "working out columns" will there ever be 2 questions answered in the same 5 minute period ?? if so my example will fail

just getting the addin which will show my page back in 2 mins

 

[shippey121]

Book1
	A	B	C	D	E	F	G
1
2
3	jim
4	09:05		09:00	09:05	09:05:00	1.00
5	09:15		09:06	09:10	09:05:00	1.00
6	09:28		09:11	09:15	09:15:00	2.00
7	09:36		09:16	09:20	09:15:00	2.00
8			09:21	09:25	09:15:00	2.00
9			09:26	09:30	09:28:00	3.00
10			09:31	09:35	09:28:00	3.00
11			09:36	09:40	09:36:00	4.00
12			09:41	09:45	09:36:00	4.00
13			09:46	09:50	09:36:00	4.00
14			09:51	09:55	09:36:00	4.00
15
Sheet1

 

[shippey121]

right now you can see my sheet, in my example i used col A for Jims times, i named the range A4:A7 to JIM, i then used cols C and D for start finish times. Col E is the Vlookup formula, if you name your ranges alter the word JIM to suit also alter the D4 for the finish time to suit, this formula can be copied down. Col E can be hidden, (helper column only) Col F has an If statement notice F4 and F5 are different you can see the formulas by clicking the cells in my example table, now to make the duplicate 1's 2's and 3's disappear we used conditiona formatting,in cell F4 select Conditional formatting for the Format Menu and select FORMULA IS and enter =F4=F3 Click the format button and change the font colour to White click ok, copy the conditional formatting down,

Please note this sheet wont take into consideration 2 times in the same 5 minute period,

the VLOOKUP formula need to be

=IF(ISERROR(VLOOKUP(D4,jim,1)),0,VLOOKUP(D4,jim,1))

if code used from example you will get errors if 1st time isnt in 1st 5 minutes
HTH

 

[ronald.gravendeel]

this can be done but will require some extra "working out columns" will there ever be 2 questions answered in the same 5 minute period ?? if so my example will fail

just getting the addin which will show my page back in 2 mins

It could be possible that the same period has two occurrences, but in that case, it's OK that the last started question is registered

 

[shippey121]

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
2								Jim		Bob		Ann
3	jim	Bob	ann		Start	Finish
4	09:06	09:03	09:02		09:00	09:05		0.00		1.00		1.00
5	09:15	09:16	09:09		09:06	09:10		1.00		1.00		2.00
6	09:28	09:31	09:17		09:11	09:15		2.00		1.00		2.00
7	09:36	09:49	09:29		09:16	09:20		2.00		2.00		3.00
8					09:21	09:25		2.00		2.00		3.00
9					09:26	09:30		3.00		2.00		4.00
10					09:31	09:35		3.00		3.00		4.00
11					09:36	09:40		4.00		3.00		4.00
12					09:41	09:45		4.00		3.00		4.00
13					09:46	09:50		4.00		4.00		4.00
14					09:51	09:55		4.00		4.00		4.00
Sheet1