Index, Match date/Name cross reference

[=HPSF=RMP]

First sheet is a list of names in A3:A10 and header dates in columns D2:N2. Each cell within this cross reference has a score associated to it.


The second sheet has the name i'm trying to reference in cell D5.
That same sheet has a date listed on cell D3.

I'm looking for two different results in cell P5 and P7 on sheet 2.

I want one result to be the cross reference of the name and date. (forumla to be in cell P5) giving me the score associated to the name and date searched.

The other result needs to be a cross reference of the name and date - 1 month. (in P7)

Hope this makes sense?

thanks

 

[Smitty]

This should be a start:

Excel Workbook
	A	B	C	D	E	F	G	H
1	Names			01/01/11	01/02/11	01/03/11	01/04/11	01/05/11
2	Bob			24	25	26	27	28
3	Chris			34	35	36	37	38
4	Tom			44	45	46	47	48
5	Fred			54	55	56	57	58
6
7	Name	Tom
8	Date	01/03/11
9	Result	46
Sheet1

Excel 2010

Cell Formulas
Range	Formula
E1	=D1+1
F1	=E1+1
G1	=F1+1
H1	=G1+1
B9	=INDEX(D2:H5,MATCH(B7,A2:A5,0),MATCH(B8,D1:H1,0))

HTH,

 

[AlphaFrog]

Sheet1

Excel Workbook
	A	D	E	F	G	H	I	J	K	L	M	N
2	Names	3/14/2011	3/21/2011	3/28/2011	4/4/2011	4/11/2011	4/18/2011	4/25/2011	5/2/2011	5/9/2011	5/16/2011	5/23/2011
3	Nicole	1	11	21	31	41	51	61	71	81	91	101
4	Renee	2	12	22	32	42	52	62	72	82	92	102
5	Francine	3	13	23	33	43	53	63	73	83	93	103
6	Mitch	4	14	24	34	44	54	64	74	84	94	104
7	Mark	5	15	25	35	45	55	65	75	85	95	105
8	Rodney	6	16	26	36	46	56	66	76	86	96	106
9	Hurcules	7	17	27	37	47	57	67	77	87	97	107
10	Therese	8	18	28	38	48	58	68	78	88	98	108
Sheet

Sheet2

Excel Workbook
	D	P
3	4/11/2011
4
5	Mark	45
6
7	Month Total	200
Sheet

 

[=HPSF=RMP]

Both work perfectly!
One question about Alpha's P7 result.

I've got it broken down by months.

1/2011 2/2011 and so on.

For the P7 I want the result to be


=INDEX(YTD!$C$3:$N$10,MATCH(D5,YTD!$A$3:$A$10,0),MATCH((MONTH(D3)-1),YTD!$C$2:$N$2,0))

So its basically subtracting a month and giving me the previous month.

Thanks to you both!

 

[AlphaFrog]

Maybe try something like this...

=INDEX(Sheet1!A2:N10, MATCH(D5,Sheet1!A2:A10,0),MATCH([COLOR="Red"]DATE(YEAR(D3),MONTH(D3)-1,DAY(D3))[/COLOR],Sheet1!A2:N2,0))

 

[=HPSF=RMP]

Perfect!

thank you!