Help with Index Match?

K

keef2

Board Regular
Joined
Jun 30, 2022
Messages
185
Office Version
  1. 365
Platform
  1. Windows
Hello,

Looking to use the weekly sales sheet to generate a summary in "summary ". That said I am simply looking to put a formula in C27 thru I36 on the summary table to populate with the appropriate value for each driver.

That said example would be for Ethan and whatever driver there is. But for Ethan i would like to have the values in cell D15 thru J15 be pulled into row 27on the "summary". Any help would be appreciated, thanks!

PS if there is also a better way to handle my date formulas to automatically always put the previous week ending dates Monday thru Sunday that would be awesome!
D5D5=Summary!$B$1-7
E5E5=Summary!$B$1-6
F5F5=Summary!$B$1-5
G5G5=Summary!$B$1-4
H5H5=Summary!$B$1-3
I5I5=Summary!$B$1-2
J5J5=Summary!$B$1-1

Tuk Prototype.xlsx
BCDEFGHIJK
1
2Tuk Tuk Weekly Sales
3
4MondayTuesdayWednesdayThursdayFridaySaturdaySundayWeekly Totals
57/4/20227/5/20227/6/20227/7/20227/8/20227/9/20227/10/2022
6Ethan SHours 55
7Tuk TukCC Fare/Tips$ 17.08$ 17.08
8Tuk TukCash Fare/Tips$ 60.00$ 60.00
9NightTotal Fare/Tips$ -$ -$ 77.08$ -$ -$ -$ -$ 77.08
10Night/TourTuk Take/Rent$ -
11ToursHours0
12ToursHourly Pay$ -$ -$ -$ -$ -$ -$ -$ -
13ToursCC Tips$ -
14TotalDriver Take$ -$ -$ 77.08$ -$ -$ -$ -$ 77.08
15TotalDriver $/Hr$ -$ -$ 15.42$ -$ -$ -$ -$ 15.42
16TotalCC $ Owed$ -$ -$ 17.08$ -$ -$ -$ -$ 17.08
17TotalOwed-Rental$ -$ -$ 17.08$ -$ -$ -$ -$ 17.08
18
19Michael SHours 7714
20Tuk TukCC Fare/Tips$ 12.47$ 13.52$ 25.99
21Tuk TukCash Fare/Tips$ 152.00$ 134.00$ 286.00
22NightTotal Fare/Tips$ -$ -$ -$ -$ 164.47$ 147.52$ -$ 311.99
23Night/TourTuk Take/Rent$ 50.00$ 50.00$ 100.00
24ToursHours0
25ToursHourly Pay$ -$ -$ -$ -$ -$ -$ -$ -
26ToursCC Tips$ -
27TotalDriver Take$ -$ -$ -$ -$ 164.47$ 147.52$ -$ 311.99
28TotalDriver $/Hr$ -$ -$ -$ -$ 23.50$ 21.07$ -$ 22.29
29TotalCC $ Owed$ -$ -$ -$ -$ 12.47$ 13.52$ -$ 25.99
30TotalOwed-Rental$ -$ -$ -$ -$ (37.53)$ (36.48)$ -$ (74.01)
31
32Kevin LHours 0
33Tuk TukCC Fare/Tips$ -
34Tuk TukCash Fare/Tips$ -
35NightTotal Fare/Tips$ -$ -$ -$ -$ -$ -$ -$ -
36Night/TourTuk Take/Rent$ -
37ToursHours0
38ToursHourly Pay$ -$ -$ -$ -$ -$ -$ -$ -
39ToursCC Tips$ -
40TotalDriver Take$ -$ -$ -$ -$ -$ -$ -$ -
41TotalDriver $/Hr$ -$ -$ -$ -$ -$ -$ -$ -
42TotalCC $ Owed$ -$ -$ -$ -$ -$ -$ -$ -
43TotalOwed-Rental$ -$ -$ -$ -$ -$ -$ -$ -
44
45Austin GHours 0
46Tuk TukCC Fare/Tips$ -
47Tuk TukCash Fare/Tips$ -
48NightTotal Fare/Tips$ -$ -$ -$ -$ -$ -$ -$ -
49Night/TourTuk Take/Rent$ -
50ToursHours0
51ToursHourly Pay$ -$ -$ -$ -$ -$ -$ -$ -
52ToursCC Tips$ -
53TotalDriver Take$ -$ -$ -$ -$ -$ -$ -$ -
54TotalDriver $/Hr$ -$ -$ -$ -$ -$ -$ -$ -
55TotalCC $ Owed$ -$ -$ -$ -$ -$ -$ -$ -
56TotalOwed-Rental$ -$ -$ -$ -$ -$ -$ -$ -
Weekly Sales
Cell Formulas
RangeFormula
D5D5=Summary!$B$1-7
E5E5=Summary!$B$1-6
F5F5=Summary!$B$1-5
G5G5=Summary!$B$1-4
H5H5=Summary!$B$1-3
I5I5=Summary!$B$1-2
J5J5=Summary!$B$1-1
B6B6=LEFT(Summary!A27,FIND(" ",Summary!A27)+1)
D9:J9,D48:J48,D35:J35,D22:J22D9=D7+D8
D12:J12,D51:J51,D38:J38,D25:J25D12=D11*Summary!$B$2
K55:K56,K45:K53,K42:K43,K32:K40,K29:K30,K19:K27,K6:K14,K16:K17K6=SUM(D6:J6)
D14:J14,D53:J53,D40:J40,D27:J27D14=D9+D12+D13
D15:K15,D54:K54,D41:K41,D28:K28D15=IFERROR(D14/D6,0)
D16:J16,D55:J55,D42:J42,D29:J29D16=D7
D17:J17,D56:J56,D43:J43,D30:J30D17=D7+D12+D13-D10
B19B19=LEFT(Summary!A28,FIND(" ",Summary!A28)+1)
B32B32=LEFT(Summary!A29,FIND(" ",Summary!A29)+1)
B45B45=LEFT(Summary!A30,FIND(" ",Summary!A30)+1)


Tuk Prototype.xlsx
ABCDEFGHIJ
24Driver Average $/HR
25DriverMondayTuesdayWednesdayThursdayFridaySaturdaySundayWeekly Avg
267/4/20227/5/20227/6/20227/7/20227/8/20227/9/20227/10/2022
27Ethan Stonis#DIV/0!
28Michael Semanic#DIV/0!
29Kevin Lorenzl#DIV/0!
30Austin Garwood#DIV/0!
31Tom Kolanowski#DIV/0!
32Matt Savide#DIV/0!
33Driver A#DIV/0!
34Driver B#DIV/0!
35Driver C#DIV/0!
36Driver D#DIV/0!
Summary
Cell Formulas
RangeFormula
C25:I26C25=C4
J27:J36J27=AVERAGE(C27:I27)
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Just bumping this up top for some possible help. Also if index match isnt the right way im open to other solutions. Thanks again!
 
Upvote 0
See if this helps:

20220712 Index Match Offset keef2.xlsx
ABCDEFGHIJ
24Driver Average $/HR
25DriverMondayTuesdayWednesdayThursdayFridaySaturdaySundayWeekly Avg
264/07/20225/07/20226/07/20227/07/20228/07/20229/07/202210/07/2022
27Ethan Stonis12345674
28Michael Semanic000012.4713.5203.712857143
29Kevin Lorenzl00000000
30Austin Garwood00000000
31Tom Kolanowski00000000
32Matt Savide00000000
33Driver A00000000
34Driver B00000000
35Driver C00000000
36Driver D00000000
Summary
Cell Formulas
RangeFormula
C25:I26C25=C4
C27:I36C27=IFERROR(INDEX('Weekly Sales'!D:D,MATCH(LEFT($A27,FIND(" ",$A27)+1),'Weekly Sales'!$B:$B,0)+10,0),0)
J27:J36J27=AVERAGE(C27:I27)
 
Upvote 0
Solution
Alex Blakenburg said:
See if this helps:

20220712 Index Match Offset keef2.xlsx
ABCDEFGHIJ
24Driver Average $/HR
25DriverMondayTuesdayWednesdayThursdayFridaySaturdaySundayWeekly Avg
264/07/20225/07/20226/07/20227/07/20228/07/20229/07/202210/07/2022
27Ethan Stonis12345674
28Michael Semanic000012.4713.5203.712857143
29Kevin Lorenzl00000000
30Austin Garwood00000000
31Tom Kolanowski00000000
32Matt Savide00000000
33Driver A00000000
34Driver B00000000
35Driver C00000000
36Driver D00000000
Summary
Cell Formulas
RangeFormula
C25:I26C25=C4
C27:I36C27=IFERROR(INDEX('Weekly Sales'!D:D,MATCH(LEFT($A27,FIND(" ",$A27)+1),'Weekly Sales'!$B:$B,0)+10,0),0)
J27:J36J27=AVERAGE(C27:I27)
Click to expand...
works like a charm thank you! Now i just need to understand better how this formula works! Thanks again.
 
Upvote 0
keef2 said:
Now i just need to understand better how this formula works!
Click to expand...

See if this helps, if not just disregard it.

1657702635559.png
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Solver works half the time for the same problem
Replies
4
Views
663
EthanP
E
O
  • Question
Helpful Tool: Reserve Spending and Budget Tracker
Replies
0
Views
358
oddzac
O
T
  • Question
Adapting Old VBA Code for PDF Printing
Replies
0
Views
669
treaves04
T
F
  • Question
Index & Match Help (I think….)
Replies
3
Views
514
FDibbins
FDibbins
T
  • Question
INDEX MATCH with multiple criteria - looking for all dates in month/range - please help!!
Replies
4
Views
6K
Weazel
Weazel

Forum statistics

Threads
1,214,649
Messages
6,120,732
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