Hi,
Thanks for a great forum - the explanation of INDEX and MATCH is very useful.
I have a spreadsheet with 3 columns.
A = Staff Number
B = Renewal Date
C = Employee
Not sure if I am going about this the right way but I have a spreadsheet (sample attached) and what I want to do is to be able to enter a staff number in cell G5 and in Cell G6 write a formula that will search the latest (MAX) renewal date for this staff number and return the name of the employee.
The data will be stored in separate worksheets
Any help would be appreciated.
You could use:
=INDEX($C$4:$C$31,MATCH(MAX(IF($A$4:$A$31=G5,$B$4:$B$31)),IF($A$4:$A$31=G5,$B$4:$B$31),0))
array entered with Ctrl+Shift+Enter. If you can sort the data by renewal date, you could simplify the formula.
Fantastic this has worked a treat thank you for the quick response this is appreciated.
