INDEX and MATCH query

[Jim Mc]

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

[mdmackillop]

Better to post a workbook - Go Advanced/Manage Attachments
Not sure about range references so may need adjusting

D5 =SUMPRODUCT(--(Sheet1!$A$1:$A$30=Sheet2!B5),--(Sheet1!$C$1:$C$30=Sheet2!$D$2),(Sheet1!F1:$F$30))
E5 =COUNTIFS(Sheet1!$A$1:$A$30,Sheet2!B5,Sheet1!C1:$C$30,Sheet2!$D$2)

[mancubus]

E5: 9

=COUNTIFS(Sheet1!C:C,$D$2,Sheet1!A:A,$B5)

if all values are 215, and you need one of them, you may want to return that value from the first matching cell:
F5: 215

=INDEX(Sheet1!A:G,MATCH($B5,Sheet1!A:A,0),6)

[Bob Phillips]

if all values are 215, and you need one of them, you may want to return that value from the first matching cell:
F5: 215

=INDEX(Sheet1!A:G,MATCH($B5,Sheet1!A:A,0),6)

You want to account for name, the list on Sheet1 with have more than just MICHAEL


=INDEX(Sheet1!$F$1:$F$30,MATCH(1,($B5=Sheet1!$A$1:$A$30)*($D$2=Sheet1!$C$1: $C$30),0),1)

array-entered

[Jim Mc]

Hi folks,

Thank you for all replies, sorry I cant actually post my workbook as my office internet settings stop me from adding attachments to webpages.

The formula for displaying the number of cases works wonderfully but I cant get the time on business cell to populate.


Perhaps my example data should have shown more than one name in the list?

The result I want is to show that on the date shown below each person spent the specified time on buisness

Mike 215
Dave 180
sue 240
Frank 90





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

Dave

010407

3

180

20



03.01.2017

LIVERPOOL

Dave

010407

3

180

20



03.01.2017

LIVERPOOL

Dave

010407

3

180

20



03.01.2017

LIVERPOOL

Sue

010407

3

240

20



03.01.2017

LIVERPOOL

Sue

010407

3

240

20



03.01.2017

LIVERPOOL

Frank

010407

3

90

20








Cheers,

Jim

[mancubus]

post the proper layout of sheet 2.
Col A: blank
Col B: dates
Col C: daily savings (1360 is in this col i think)
Col D: total (you say you have no problem with this)
Col E: occurences
Col F: time

do you change the name in D2 for all the people?
or do you have a table for each name?

are business times the same (215 michael, 180 dave, etc) or is it an average based on the name?


you may email your sample workbook to your personal account and upload it at home.

[Jim Mc]

total

No. Cases

time at court














































DATE


Daily total


MICHAEL


DAVE


SUE

FRANK



01.01.2017


£ -


£ -




£ -




£ -



£ -





02.01.2017


£ -


£ -




£ -




£ -



£ -





03.01.2017


£ 5,742.50


£ 1,360.00

3

215


£ -

3

180


£ -

2

240

£ -

1

90



04.01.2017


£ 4,084.46


£ -




£ -




£ 771.50



£ 659.25





05.01.2017


£ 4,354.46


£ -




£ -




£ 418.00



£ 267.75





06.01.2017


£ 6,984.00


£ 2,275.00




£ -




£ 863.50



£ 581.50





07.01.2017


£ -


£ -




£ -




£ -



£ -






Sorry, my emails are seriously restricted as to who and what I can send, this is the best I can do from my desk I'm afraid.

columns run as follows

A blank
B date
C blank
D Daily total
E Blank
F total
G No Cases
H time at court
I blank
then the following columns repeat F through I for each person across the sheet.

Business times vary per day but for reasons beyond my control the total time has to be entered next to each entry on sheet 1

I hope this makes things clear now?

Jim

[mdmackillop]

This correct?

[Jim Mc]

Sorry for the delay I have been on leave from work.

Thank you for the reply and yes that is the way it looks. The totals are not correct as there are others on the table but I reduced it (and changed names) for obvious reasons.

Jim