Vlookup mixed with IF, looking at a date.

Good evening happy campers.

Please see attached.

Sheet1 Cell A1 - todays date (=today() )
C3 - (formula here)
Sheet2 Range A:B - 'DATA'

I would like C3 (sheet1) to show me the name from sheet 2 that is closest to the next date on sheet2 (closest to A1 Sheet1)

Initially i thought a nested IF within Vlookup would do it but i cant get my head round it, could someone kindly turn the light on for me?

kind regards, galvinpaddy

=VLOOKUP(value, matrix, num, TRUE) can be used on sorted arrays (such as you have here) to deliver the largest value that is less than or equal to the lookup... But that's not quite what you want, huh?

I've built what I think you want using MATCH and INDEX with an IF... didn't actually use VLOOKUP, but that's just the way the cookie crumbled. (See attached).

I used five cells, and then just stepped cell references backwards until I was only referring to sheet1!A1 and the array--and since sheet1A1 is just = TODAY(), you could replace those too, and feed this function just the array as the argument and it would automatically spit out what's closest to today. That might be pretty opaque to look at though, be warned.

Hi Both, thanks for your input
DILIPandey - Thanks, but i would need the next name in the list, the true part of vlookup only matches the nearest.
Ben_Hensel - thanks, works great until i input the date in A1 as the same date on Sheet2, again, i would need it to show me the next coming Name (following the date in A1)
Apologies both, my explanation may not have been tip top blurry eyes after sitting in-front of laptop all day
Kind Regards, galvinpaddy

Good morning all.

If it is easier the formula can look at the next name in the list and show that value.

Kind regards, galvinpaddy.

Good morning Fotis,

Thankyou for your input.
Please see attached, i have an #N/A error.
PLease advise

Kind Regards, Galvinpaddy

Hi, thanks fo rthe input.
What i need is for the formula to show me the next date and/or name in the list, following on from whatever date is added into cell A1 (sheet1)

Ho Fotis,

Yes, your formula works fine if the date matches one from Sheet2.
But - the date in Sheet1 A1, is set as =TODAY() so this will change according to the date it is opened, so i would then need to see the next name on the list.
Kind regards, galvinpaddy.

yes, thank-you very much indeed for your help!!!!

Apologies Fotis,

I have tried to modify the formula to fit into the sheet i need it for and have failed

I have changed your code to -

=IFERROR(INDEX(Rota!B1:B100,MATCH(Meeting!C1,Rota!A1:A100,1)+1),"")

With Rota! replacing Sheet2 and Meeting! replacing sheet1, which i assumed would still work, but the cell stays blank.
The data on sheet 2 is the same, but name1 name2 have been replaced with the peoples first & surnames.
The date formats are the same.

gggrrr, could i please ask for a little more help? thanks again.

Hi Fotis,

Thanks for your help