Index with two match formulas

[Upetr]

I have a timesheet with dates on the one page. Each user has a timesheet and I want to do a lookup to another sheet which contains the duration spent at work.

E F G
2011-08-01 2011-08-02 2011-08-01

Now I have the look up sheet (Sheet1) which contains everyone's names, dates for the month and duration.

A B C
Andrew 2011-08-01 7h40
Andrew 2011-08-02 7h45
Andrew 2011-08-03 8h30
John 2011-08-01 8h15
John 2011-08-02 7h30

Now I need to get the durations for John for the different days in the columns below the dates. In this case into E2,F2,G2.
I use the Match formula to get the row number =MATCH("John",Sheet1!A:A,) and I get the first occurence which is 4. Then I use the same formula to get the date but =MATCH(E1,Sheet1!D:D,) but I get row 1 because I don't know how to combine the two formulas into one. I have used index to try and get the results of the column number - but wasn's lucky in combining them. How can I do this - or is there a better way? Thanks.

 

[T. Valko]

I have a timesheet with dates on the one page. Each user has a timesheet and I want to do a lookup to another sheet which contains the duration spent at work.

E F G
2011-08-01 2011-08-02 2011-08-01

Now I have the look up sheet (Sheet1) which contains everyone's names, dates for the month and duration.

A B C
Andrew 2011-08-01 7h40
Andrew 2011-08-02 7h45
Andrew 2011-08-03 8h30
John 2011-08-01 8h15
John 2011-08-02 7h30

Now I need to get the durations for John for the different days in the columns below the dates. In this case into E2,F2,G2.
I use the Match formula to get the row number =MATCH("John",Sheet1!A:A,) and I get the first occurence which is 4. Then I use the same formula to get the date but =MATCH(E1,Sheet1!D:D,) but I get row 1 because I don't know how to combine the two formulas into one. I have used index to try and get the results of the column number - but wasn's lucky in combining them. How can I do this - or is there a better way? Thanks.

What version of Excel are you using?

 

[Upetr]

I am using 2007.

 

[T. Valko]

I am using 2007.

Try something like this...

Your raw data table:

Book1
	A	B	C
1	Name	Date	Time
2	Andrew	8/1/2011	7h40
3	Andrew	8/2/2011	7h45
4	Andrew	8/3/2011	8h30
5	John	8/1/2011	8h15
6	John	8/2/2011	7h30
Sheet1

Your summary table:

Book1
	D	E	F	G
1	_	8/1/2011	8/2/2011	8/3/2011
2	Andrew	7h40	7h45	8h30
3	John	8h15	7h30	_
Sheet2

This array formula** entered in E2:

=IFERROR(INDEX(Sheet1!$C$2:$C$6,MATCH(1,IF(Sheet1!$A$2:$A$6=$D2,IF(Sheet1!$B$2:$B$6=E$1,1)),0)),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy across to G2 then down as needed.

 

[Upetr]

You're a genius!

 

[T. Valko]

You're a genius!

You're welcome. Thanks for the feedback!