Return results in a table

J

jeffdlinn

New Member
Joined
Nov 18, 2013
Messages
29
Office Version
  1. 365
Platform
  1. Windows
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?

A1BCDEFGHIJ
2StartEndMondayTuesdayWednesdayThursdayFridaySaturdaySunday
310:00:00 AM10:59:59 AM$ - $ - $ - $ - $ - $ - $ -
411:00:00 AM11:59:59 AM$ - $ - $ - $ - $ - $ - $ -
512:00:00 PM12:59:59 PM$ - $ - $ - $ - $ - $ 25.00$ 25.00
61:00:00 PM1:59:59 PM$ - $ - $ - $ - $ - $ 25.00$ 25.00
72:00:00 PM2:59:59 PM$ - $ - $ - $ - $ - $ 25.00$ 25.00
83:00:00 PM3:59:59 PM$ - $ - $ - $ - $ - $ 25.00$ 25.00
94:00:00 PM4:59:59 PM$ - $ - $ - $ - $ 35.00$ 35.00$ 35.00
105:00:00 PM5:59:59 PM$ 25.00$ - $ - $ 25.00$ 35.00$ 35.00$ 35.00
116:00:00 PM6:59:59 PM$ 25.00$ - $ - $ 25.00$ 35.00$ 35.00$ 35.00
127:00:00 PM7:59:59 PM$ 35.00$ - $ - $ 25.00$ 45.00$ 45.00$ 45.00
138:00:00 PM8:59:59 PM$ 35.00$ - $ - $ 25.00$ 45.00$ 45.00$ 45.00
149:00:00 PM9:59:59 PM$ 45.00$ - $ - $ 25.00$ 45.00$ 45.00$ 45.00
1510:00:00 PM10:59:59 PM$ - $ - $ - $ - $ 45.00$ 45.00$ -
1611:00:00 PM11:59:59 PM$ - $ - $ - $ - $ - $ - $ -
1712:00:00 AM12:59:59 AM$ - $ - $ - $ - $ - $ - $ -
18
19
20StartStopRate
21Monday10:00:00 AM10:59:59 AM
22Monday11:00:00 AM11:59:59 AM
23Monday12:00:00 PM12:59:59 PM
24Monday1:00:00 PM1:59:59 PM
25Monday2:00:00 PM2:59:59 PM
26Monday3:00:00 PM3:59:59 PM
27Monday4:00:00 PM4:59:59 PM
28Monday5:00:00 PM5:59:59 PM
29Monday6:00:00 PM6:59:59 PM
30Monday7:00:00 PM7:59:59 PM
31Monday8:00:00 PM8:59:59 PM
32Monday9:00:00 PM9:59:59 PM
33Monday10:00:00 PM10:59:59 PM
34Monday11:00:00 PM11:59:59 PM
35Monday12:00:00 AM12:59:59 AM
 
Last edited:

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college
Sort by date Sort by votes
What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)
 
Upvote 0
Excel Formula: 
=INDEX($D$3:$J$17,MATCH(C30,$B$3:$B$17,0),MATCH(B30,$D$2:$J$2,0))
 
Upvote 0
Thanks for updating your profile. Another option is
+Fluff 1.xlsm
ABCDEFGHIJ
1A1BCDEFGHIJ
22StartEndMondayTuesdayWednesdayThursdayFridaySaturdaySunday
3310:00:00 AM10:59:59 AM$ -$ -$ -$ -$ -$ -$ -
4411:00:00 AM11:59:59 AM$ -$ -$ -$ -$ -$ -$ -
5512:00:00 PM12:59:59 PM$ -$ -$ -$ -$ -$ 25.00$ 25.00
661:00:00 PM1:59:59 PM$ -$ -$ -$ -$ -$ 25.00$ 25.00
772:00:00 PM2:59:59 PM$ -$ -$ -$ -$ -$ 25.00$ 25.00
883:00:00 PM3:59:59 PM$ -$ -$ -$ -$ -$ 25.00$ 25.00
994:00:00 PM4:59:59 PM$ -$ -$ -$ -$ 35.00$ 35.00$ 35.00
10105:00:00 PM5:59:59 PM$ 25.00$ -$ -$ 25.00$ 35.00$ 35.00$ 35.00
11116:00:00 PM6:59:59 PM$ 25.00$ -$ -$ 25.00$ 35.00$ 35.00$ 35.00
12127:00:00 PM7:59:59 PM$ 35.00$ -$ -$ 25.00$ 45.00$ 45.00$ 45.00
13138:00:00 PM8:59:59 PM$ 35.00$ -$ -$ 25.00$ 45.00$ 45.00$ 45.00
14149:00:00 PM9:59:59 PM$ 45.00$ -$ -$ 25.00$ 45.00$ 45.00$ 45.00
151510:00:00 PM10:59:59 PM$ -$ -$ -$ -$ 45.00$ 45.00$ -
161611:00:00 PM11:59:59 PM$ -$ -$ -$ -$ -$ -$ -
171712:00:00 AM12:59:59 AM$ -$ -$ -$ -$ -$ -$ -
1818
1919
2020StartStopRate
2121Monday10:00:00 AM10:59:59 AM$ -
2222Monday11:00:00 AM11:59:59 AM$ -
2323Monday12:00:00 PM12:59:59 PM$ -
2424Monday1:00:00 PM1:59:59 PM$ -
2525Monday2:00:00 PM2:59:59 PM$ -
2626Monday3:00:00 PM3:59:59 PM$ -
2727Monday4:00:00 PM4:59:59 PM$ -
2828Monday5:00:00 PM5:59:59 PM$ 25.00
2929Monday6:00:00 PM6:59:59 PM$ 25.00
3030Monday7:00:00 PM7:59:59 PM$ 35.00
3131Monday8:00:00 PM8:59:59 PM$ 35.00
3232Monday9:00:00 PM9:59:59 PM$ 45.00
3333Monday10:00:00 PM10:59:59 PM$ -
3434Monday11:00:00 PM11:59:59 PM$ -
3535Monday12:00:00 AM12:59:59 AM$ -
36
Lists
Cell Formulas
RangeFormula
E21:E35E21=FILTER(FILTER($D$3:$J$17,$B$3:$B$17=C21),$D$2:$J$2=B21)
 
Upvote 0
You must log in or register to reply here.

Similar threads

Z
  • Solved
Formula to find 'Part of the Day' from the Time Column
Replies
8
Views
296
Zee996
Z
L
  • Question
IF statement for Time Ranges
Replies
1
Views
372
awoohaw
awoohaw
U
  • Question
Help - Countif and sumif
2
Replies
10
Views
657
ur_uncle
U
Q
  • Solved
Calculating total number of hours within a period, counting only specific days of the week, and specific time intervals.
Replies
8
Views
618
Dave Patton
D
E
  • Question
Formatting time
Replies
5
Views
482
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top