I have the following table A1 to J17 and need to look up the rate based on the Start Time and Day of week. So for example in cell e30 the rate displayed should be $35.00. I'm trying to write a formula that references cell B30 (Monday), cell C30 (7PM) then looks at the table in A1:J17 and returns the result in cell d12 of 35. I'm trying to use the index function and keep getting an error. Any thoughts from all you excel wizards?
A1 | B | C | D | E | F | G | H | I | J |
2 | Start | End | Monday | Tuesday | Wednesday | Thursday | Friday | Saturday | Sunday |
3 | 10:00:00 AM | 10:59:59 AM | $ - | $ - | $ - | $ - | $ - | $ - | $ - |
4 | 11:00:00 AM | 11:59:59 AM | $ - | $ - | $ - | $ - | $ - | $ - | $ - |
5 | 12:00:00 PM | 12:59:59 PM | $ - | $ - | $ - | $ - | $ - | $ 25.00 | $ 25.00 |
6 | 1:00:00 PM | 1:59:59 PM | $ - | $ - | $ - | $ - | $ - | $ 25.00 | $ 25.00 |
7 | 2:00:00 PM | 2:59:59 PM | $ - | $ - | $ - | $ - | $ - | $ 25.00 | $ 25.00 |
8 | 3:00:00 PM | 3:59:59 PM | $ - | $ - | $ - | $ - | $ - | $ 25.00 | $ 25.00 |
9 | 4:00:00 PM | 4:59:59 PM | $ - | $ - | $ - | $ - | $ 35.00 | $ 35.00 | $ 35.00 |
10 | 5:00:00 PM | 5:59:59 PM | $ 25.00 | $ - | $ - | $ 25.00 | $ 35.00 | $ 35.00 | $ 35.00 |
11 | 6:00:00 PM | 6:59:59 PM | $ 25.00 | $ - | $ - | $ 25.00 | $ 35.00 | $ 35.00 | $ 35.00 |
12 | 7:00:00 PM | 7:59:59 PM | $ 35.00 | $ - | $ - | $ 25.00 | $ 45.00 | $ 45.00 | $ 45.00 |
13 | 8:00:00 PM | 8:59:59 PM | $ 35.00 | $ - | $ - | $ 25.00 | $ 45.00 | $ 45.00 | $ 45.00 |
14 | 9:00:00 PM | 9:59:59 PM | $ 45.00 | $ - | $ - | $ 25.00 | $ 45.00 | $ 45.00 | $ 45.00 |
15 | 10:00:00 PM | 10:59:59 PM | $ - | $ - | $ - | $ - | $ 45.00 | $ 45.00 | $ - |
16 | 11:00:00 PM | 11:59:59 PM | $ - | $ - | $ - | $ - | $ - | $ - | $ - |
17 | 12:00:00 AM | 12:59:59 AM | $ - | $ - | $ - | $ - | $ - | $ - | $ - |
18 | |||||||||
19 | |||||||||
20 | Start | Stop | Rate | ||||||
21 | Monday | 10:00:00 AM | 10:59:59 AM | ||||||
22 | Monday | 11:00:00 AM | 11:59:59 AM | ||||||
23 | Monday | 12:00:00 PM | 12:59:59 PM | ||||||
24 | Monday | 1:00:00 PM | 1:59:59 PM | ||||||
25 | Monday | 2:00:00 PM | 2:59:59 PM | ||||||
26 | Monday | 3:00:00 PM | 3:59:59 PM | ||||||
27 | Monday | 4:00:00 PM | 4:59:59 PM | ||||||
28 | Monday | 5:00:00 PM | 5:59:59 PM | ||||||
29 | Monday | 6:00:00 PM | 6:59:59 PM | ||||||
30 | Monday | 7:00:00 PM | 7:59:59 PM | ||||||
31 | Monday | 8:00:00 PM | 8:59:59 PM | ||||||
32 | Monday | 9:00:00 PM | 9:59:59 PM | ||||||
33 | Monday | 10:00:00 PM | 10:59:59 PM | ||||||
34 | Monday | 11:00:00 PM | 11:59:59 PM | ||||||
35 | Monday | 12:00:00 AM | 12:59:59 AM |
Last edited: