Index Match

[Daviiiiiiddd]

I'm having a problem with the index-match formula. I have two sheets: one with dates and address and another with dates address and results. I need to make a formula to grab the results from the second sheet based on the MOST RECENT DATE.

=INDEX(test3!C:C,MATCH(test2!B1,test3!B:B,0))

2013-03-14	1566 Newport	"Index-Match Formula Here"
2013-03-14	1546 Pinnacle	"Index-Match Formula Here"
2013-03-14	256 Dawn	"Index-Match Formula Here"
2013-03-14	498 Savannah	"Index-Match Formula Here"

<tbody>
</tbody>

I am using the following results and I need the most recent date result:

2013-03-14	1566 Newport	Win
2013-03-8	1546 Pinnacle	Win
2013-03-10	256 Dawn	Postponed
2013-03-14	256 Dawn	Win

<tbody>
</tbody>

I am currently using:

=INDEX(test3!C:C,MATCH(test2!B1,test3!B:B,0))

And it is NOT returning the the result for the latest date. For example: 256 Dawn shows, "Postponed" and not "win", for some reason, my formula is returning the postponed and not the win (I think because I didn't tell it to factor in the date). Can somebody please help me with this?????????????

 

[venkat1926]

index match picks up first one that us first "dawn"for the date 2013-03-10

sort test 3 accorindg to cilumn A newest to oldest the formula will give you what you want.



test3

*	A	B	C
1	3/14/2013	1566 Newport	Win
2	3/14/2013	256 Dawn	Win
3	3/10/2013	256 Dawn	Postponed
4	3/8/2013	1546 Pinnacle	Win

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:132px;"><col style="width:131px;"><col style="width:127px;"></colgroup><tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Welcome to the MrExcel board!

Excel Workbook
	A	B	C
1	Date	Address	Result
2	14/03/13	1566 Newport	Win
3	8/03/13	1546 Pinnacle	Win
4	10/03/13	256 Dawn	Postponed
5	14/03/13	256 Dawn	Win
6
test3

The formulas in C2:D2 are copied down.
These are array formulas so should be entered without the {} but confirmed with Ctrl+Shift+Enter, not just Enter. If confirmed correctly, Excel will insert the {}. The formulas can then be copied down.
You could use either formula format but although my column D formula is somewhat longer than my column C formula, if you can restrict the number of rows being calculated as I have in the column D formula it will calculate much faster.
For example (I'm using Excel 2010) my 4 formulas in column C take over 200 times as long to calculate as my 4 formulas in column D.

Excel Workbook
	A	B	C	D
1	Date	Address	Latest Result	Latest Result
2	14/03/13	1566 Newport	Win	Win
3	14/03/13	1546 Pinnacle	Win	Win
4	14/03/13	256 Dawn	Win	Win
5	14/03/13	498 Savannah
Results