Return next future date (or today) for a person from list of names and dates

[Gary Skelton]

Hi all,

Hope someone can help me.

I have a list of students and their exam dates (amongst other things).

I would like to lookup a particular name (say in A1) down a list of names A3-Axxx and return the date from B3-Bxxx which is next (today or later).

I've attached a simple example in the hope that someone can help.

Thanks in advance

Regards

Gaz

 

[Nebu]

Hi:

Is this what you are looking for
its a CSE formula ,execute using Control+Shift+Enter Key.

Thanks

 

[tomas]

Hi Formula in C1 will be working but ist suppose that dates are sorted as they are now

 

[Gary Skelton]

Hi both,

You are stars! Just looking at them and figuring out how they work - just goes to show there's always several ways to crack an egg in Excel.

{=SMALL(IF($A$1=$A$4:$A$34,$B$4:$B$34),2)}
I think this one only brings the 2nd date in the list rather than the next date from today.

{=INDEX(B4:B34,MATCH(1,(A4:A34=A1)*(B4:B34>=TODAY()),0))}
This one definitely works. I will have a look at it in the (unlikely scenario) that the dates are not in order...

Cheers!

Regards

Gary

 

[Nebu]

Forgot to check for today, here is the modified formula

{=SMALL(IF($A$1=$A$4:$A$34,IF($B$4:$B$34>=TODAY(),$B$4:$B$34)),1)}

Thanks

 

[Gary Skelton]

Thanks again.

Managed to get this into my spreadsheet along with a calculation of the last exam and also a table of all the exams with past exams shaded as complete, and a nice little graph. My boss likes it and that's what counts,.

Thanks both for your help.

Regards

Gary