Latest result before a certain date

[mikewray]

Hi everyone, I'm battling with this one...would like an Index Match formula to find the last test result before a certain date for a certain userid e.g. at Oct 31 mchansa04 result of her last test was 60% written on 18th Sept. For techima05 its 68%. Thanks in advance!

Excel 2010
	A	B	C	D	E	F	G
1	userid	mchansa04
2	Date point	31/10/15
3	Last score	* formula*	e.g. mchansa04 should return 60%
4
5	Data Table
6	Date	First Name	Surname	User ID	Centre	Points	Percent
7	07/11/15	Maureen	Chansa	mchansa04	KAS	38	63%
8	18/09/15	Moureen	Chansa	mchansa04	KAS	36	60%
9	23/07/15	Moureen	Chansa	mchansa04	KAS	19	32%
10	07/10/15	Tione	Chima	tchima05	KAS	41	68%
11	18/09/15	Tione	Chima	tchima05	KAS	33	55%
12	23/07/15	Tione	Chima	tchima05	KAS	27	45%
baseline tests

I found a thread that did something similar but could not make it work for me. http://www.mrexcel.com/forum/excel-questions/528025-last-date-before-certain-date.html

 

[AhoyNC]

Try:
Change ranges to match your data.
This is an array formula and must be entered with CTRL-SHIFT-ENTER (command-return on MAC).

Excel Workbook
	A	B	C	D	E	F	G
1	userid	mchansa04
2	Date point	10/31/2015
3	Last score	60%
4
5	Data Table
6	Date	First Name	Surname	User ID	Centre	Points	Percent
7	11/7/2015	Maureen	Chansa	mchansa04	KAS	38	63%
8	9/18/2015	Moureen	Chansa	mchansa04	KAS	36	60%
9	7/23/2015	Moureen	Chansa	mchansa04	KAS	19	32%
10	10/7/2015	Tione	Chima	tchima05	KAS	41	68%
11	9/18/2015	Tione	Chima	tchima05	KAS	33	55%
12	7/23/2015	Tione	Chima	tchima05	KAS	27	45%
13
Sheet

 

[mikewray]

Nailed, it thank you so much! Can I be cheeky and ask for two other related formulas that return:

The last (latest) test score and test date for a userid e.g. mchansa04 would be 63% on 11/7/15
The first (initial) test score they got and the test date for a userid e.g. tchima05 would be 45% on 7/23/15

If the data has to be ordered in ascending date order that can be done, preferably if free of this constraint. Thanks again!

 

[AhoyNC]

Try:
These are array formulas and must be entered with CTRL-SHIFT-ENTER (command-return on MAC).

Excel Workbook
	A	B	C	D	E	F	G
1	userid	mchansa04
2	Date point	10/31/2015		Latest Date	Latest Score	Initial Date	Initial Score
3	Last score	60%		11/7/2015	63%	7/23/2015	32%
4
5	Data Table
6	Date	First Name	Surname	User ID	Centre	Points	Percent
7	11/7/2015	Maureen	Chansa	mchansa04	KAS	38	63%
8	9/18/2015	Moureen	Chansa	mchansa04	KAS	36	60%
9	7/23/2015	Moureen	Chansa	mchansa04	KAS	19	32%
10	10/7/2015	Tione	Chima	tchima05	KAS	41	68%
11	9/18/2015	Tione	Chima	tchima05	KAS	33	55%
12	7/23/2015	Tione	Chima	tchima05	KAS	27	45%
Sheet

 

[mikewray]

Well if no-one has told you that you awesome today yet, let me be the first. Thank you so much!!!

 

[AhoyNC]

You're welcome. Thanks for the feedback.