Table Lookup Solution needed

[jmoconnell]

I am trying to resolve a long list of time entries that fall between the Begin and End time intervals and therefore return the TOD label (HSR, HR etc.).

I have this table:
TOD Begin End
HSR 0:00 5:59
HR 6:00 6:59
HN 7:00 8:59
HD 9:00 11:59
HN 12:00 13:59
HD 14:00 17:59
HN 18:00 20:59
HR 21:00 23:59

I have tried the following:

1. A combination of nested IF, and, or statements that would have worked but I could not stay under the limit.

2. I examined a number of Vlookup approaches but was unable to identify a solution. I can rearrange the table any way I want to.

3. I examined a number of Offset and Match statements but again was unable to identify a solution.

4. I also tried to use named formula's in a effort to break up the nested if's into two pirces each under the limit. Then I tried to create a master formula by using the two named ranges as single formula.

I am fairly new at Excel so I may not have the experience to see the forest thru the trees or I may just be missing the appropriate use of syntax etc.

Any help on solving this problem would be greatly appreciated.

Thanks,

Joe

 

[Aladin Akyurek]

On 2002-02-17 04:14, jmoconnell wrote:
I am trying to resolve a long list of time entries that fall between the Begin and End time intervals and therefore return the TOD label (HSR, HR etc.).

I have this table:
TOD Begin End
HSR 0:00 5:59
HR 6:00 6:59
HN 7:00 8:59
HD 9:00 11:59
HN 12:00 13:59
HD 14:00 17:59
HN 18:00 20:59
HR 21:00 23:59<snip>

Joe

A 2-column table will suffice: Drop the 3rd column and exchange the 1st and the 2nd from your original:

0:00 HSR
6:00 HR
7:00 HN
9:00 HD
12:00 HN
14:00 HD
18:00 HN
21:00 HR

Select all of the cells of this new table, go to the Name Box on the Formula Bar, and type TODS followed by enter.

Assuming that you have a time entry in A1 that you want to classify,

in B1 enter: =VLOOKUP(A1,TODS,2)
This message was edited by Aladin Akyurek on 2002-02-17 04:41

 

[jmoconnell]

Thank you for your quick response.

If I understand correctly vlookup will process serially thru a sequential number pattern and accept the last defined label.

Thanks

Joe