Index, Match date/Name cross reference

=HPSF=RMP

Board Regular
Joined
Jun 9, 2004
Messages
188
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
 

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.
This should be a start:
Excel Workbook
ABCDEFGH
1Names01/01/1101/02/1101/03/1101/04/1101/05/11
2Bob2425262728
3Chris3435363738
4Tom4445464748
5Fred5455565758
6
7NameTom
8Date01/03/11
9Result46
Sheet1
Excel 2010
Cell Formulas
RangeFormula
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,
 
Upvote 1
Sheet1
Excel Workbook
ADEFGHIJKLMN
2Names3/14/20113/21/20113/28/20114/4/20114/11/20114/18/20114/25/20115/2/20115/9/20115/16/20115/23/2011
3Nicole1112131415161718191101
4Renee2122232425262728292102
5Francine3132333435363738393103
6Mitch4142434445464748494104
7Mark5152535455565758595105
8Rodney6162636465666768696106
9Hurcules7172737475767778797107
10Therese8182838485868788898108
Sheet
Sheet2
Excel Workbook
DP
34/11/2011
4
5Mark45
6
7Month Total200
Sheet
 
Upvote 0
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!
 
Last edited:
Upvote 0
Maybe try something like this...
Code:
=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))
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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