Formula

[Nishg]

Please help me on below,

I need to pick the rate in column S for the dates listed in column D 23 to D 28.

Logic is, If the date is < 30/06 then year in the date
eg: 27/05/2014 year is 2014
If the date is > 30/06 then year plus 1
eg: 22/07/2016 year is 2017

Once I get that I need to may be VLOOK up column D to S and pick the rate in Column "S"

 

[Alex Blakenburg]

You can obviously use Index/Match or Xlookup as well but since you mentioned VLookup, start with these.

This assumes the formula is on Row 23

=VLOOKUP(IF(MONTH($D23)>6,YEAR($D23)+1,YEAR($D23)),$D$2:$S$18,16,FALSE)

If you want to, you can reduce this to the following:-
This relies on
MONTH($D23)>6 = TRUE = 1
MONTH($D23)>6 = FALSE= 0

=VLOOKUP(YEAR($D23)+(MONTH($D23)>6),$D$2:$S$18,16,FALSE)

 

[Nishg]

=VLOOKUP(IF(MONTH($D23)>6,YEAR($D23)+1,YEAR($D23)),$D$2:$S$18,16,FALSE)

This is great. Thank you so much Alex.

 

[Alex Blakenburg]

My pleasure, thanks for the feedback.

 

[jasonb75]

Sticking with vlookup, another way that should work

=VLOOKUP(YEAR(EDATE($D23,6)),$D$2:$S$18,16,0)

You can obviously use Index/Match or Xlookup as well

I think that we could probably come up with 101 ways to answer this question, Alex. As there are no duplicate years in D2:D18 Sumifs and other similar functions will also work, as would filter and probably a few other things.