Most Recent Result

[Takes2ToTango]

I have been trying to create a forumla but I only seem to get errors once I hit enter. My goal is to use the lookup date and find the most recent one depending on which person is selected. For example, if Basil is selected the most recent date would be 06/09 and the result 45. However, if the person is changed to Steve, then the most recent date would change to 07/05 and the result 65. I have attempted arrays but nothing seems to deliver the right result.

 

[Fluff]

Can you post some realistic data, showing more than one row for each name.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Takes2ToTango]

Can you post some realistic data, showing more than one row for each name.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

Book1
	F	G	H
3	Last Date Recorded	Name of Tester	Result
4	15/01/2004	Mick	25
5	20/08/2023	Mick	80
6	25/07/2023	Mick	45
7	20/07/2020	Basil	60
8	06/06/2023	Basil	48
9	07/06/2023	Basil	95
10	09/01/2023	Sue	33
11	18/05/2023	Sue	15
12	23/08/2023	Sue	68
13	01/01/2019	Gaz	45
14	29/07/2023	Gaz	77
15	15/08/2023	Gaz	95
16
17	Lookup Date	Lookup Tester	Result
18	20/09/2023	Basil
Sheet1

Cell Formulas
Range	Formula
F18	F18	=TODAY()

here is the mini sheet with the contents

 

[Fluff]

Thanks for that, as the dates don't seem to be in order try

Fluff.xlsm
	F	G	H
1
2
3	Last Date Recorded	Name of Tester	Result
4	15/01/2004	Mick	25
5	20/08/2023	Mick	80
6	25/07/2023	Mick	45
7	20/07/2020	Basil	60
8	06/06/2023	Basil	48
9	07/06/2023	Basil	95
10	09/01/2023	Sue	33
11	18/05/2023	Sue	15
12	23/08/2023	Sue	68
13	01/01/2019	Gaz	45
14	29/07/2023	Gaz	77
15	15/08/2023	Gaz	95
16
17	Lookup Date	Lookup Tester	Result
18	20/09/2023	Basil	95
Sheet5

Cell Formulas
Range	Formula
F18	F18	=TODAY()
H18	H18	=TAKE(SORT(FILTER(F4:H15,G4:G15=G18),1,-1),1,-1)

 

[Takes2ToTango]

Great thank you, that works perfect! How does the end section of =TAKE work? So I can remember for future

 

[Fluff]

It's taking the last column.

 

[Takes2ToTango]

Great, thank you

 

[Fluff]

You're welcome & thanks for the feedback.