-
INDEX and MATCH query
Hi folks,
Thank you for letting me join and taking the time to look over my query.
I have a workbook that consists of two sheets, sheet 1 collects the data, sheet 2 compiles it. I hopefully have included some test data below of each sheet
Sheet 1
Date |
Location |
Name |
Grade |
Level |
Time on Business (mins) |
Travel Time (mins) |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
03.01.2017 |
LIVERPOOL |
MICHAEL |
010407 |
3 |
215 |
20 |
Sheet 2
|
|
|
total |
No. Cases |
time at court |
|
|
|
|
|
|
|
DATE |
Daily savings total |
MICHAEL |
|
01.01.2017 |
|
|
|
|
|
02.01.2017 |
|
|
|
|
|
03.01.2017 |
|
£ 1,360.00 |
9 |
215 |
|
04.01.2017 |
|
|
|
|
|
05.01.2017 |
|
|
|
|
|
06.01.2017 |
|
|
|
|
|
07.01.2017 |
|
|
|
|
|
08.01.2017 |
|
|
|
|
Column letters are A, B, C etc. in both cases and rows are 1, 2, 3 etc.
Column D on sheet 2 totals all the values from another part of the real sheet and is not the problem.
what I want to do is :
Total all the cases covered by Michael on 03.01.2017 from sheet 1 and show that total in E5 on sheet 2 -in this example 9 cases.
Show his time at business on 03.01.2017 from sheet 1 in F5 on sheet 2 - not the total of 9*215 just as shown in the example 215 [I need 215 next to each record on sheet 1 for elsewhere in the workbook]
What formula do I need for E5 and F5 in order to calculate this? I have tried several combinations of INDEX, MATCH, LOOKUP and even praying for guidance but keep getting errors in my formula.
Please help as I have been working on this for several days and getting quite frustrated with it. I am not back at my desk until Monday so will be unable to respond until then.
Cheers,
Jim
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules