VLOOKUP (or like) on a range?

ronald.gravendeel

New Member
Joined
Jun 13, 2005
Messages
7
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>
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

shippey121

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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

Well-known Member
Joined
Mar 21, 2006
Messages
1,001
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
Code:
=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

New Member
Joined
Jun 13, 2005
Messages
7
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

Well-known Member
Joined
Mar 21, 2006
Messages
1,001

Watch MrExcel Video

Forum statistics

Threads
1,112,937
Messages
5,543,093
Members
410,583
Latest member
gazz57
Top