gacto
asked on
Excel Formula
I have a spreadsheet of dates and values that I need to perform a lookup on. Here is my data structure:
Column B - dates in Ascending order
Column J - numeric values
My problem is that I can't perform a standard lookup where I have a known value in column b that will return a value from column j. The lookups that I need to perform are based on calendar years. So for example, in the attached spreadsheet if I were doing my lookup for 2013 value I need returned is $35,904.58, if I were doing my lookup for 2014 the value I need returned is $34,589.33.
So what I need is a formula that will look for the maximum date in column b for a given year, and then return the value in the corresponding position in column j.
1098TestFormulas.xlsx
Column B - dates in Ascending order
Column J - numeric values
My problem is that I can't perform a standard lookup where I have a known value in column b that will return a value from column j. The lookups that I need to perform are based on calendar years. So for example, in the attached spreadsheet if I were doing my lookup for 2013 value I need returned is $35,904.58, if I were doing my lookup for 2014 the value I need returned is $34,589.33.
So what I need is a formula that will look for the maximum date in column b for a given year, and then return the value in the corresponding position in column j.
1098TestFormulas.xlsx
You can also use this formula in e.g. L (doesn't matter) to get the most recent value of the corresponding year:
=VLOOKUP(DATE(YEAR(C:C)+1, 1,1),C:J, 8)
=VLOOKUP(DATE(YEAR(C:C)+1,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
1098TestFormulas-V1.xlsx
This uses a combination of an array formula in one cell to determine the max date value within the year, and the MATCH and INDIRECT functions in a second cell to return the relevant value from column J.
This is the array formula (which will return an error if not entered as an array formula using CTRL+SHIFT+ENTER on Windows or CMD+SHIFT+ENTER on Mac). The formula will have curly braces around it if it is an array.
=MAX(IF(YEAR($B$1:$B$24)=A
This is the formula that goes next to it and refers to it to locate the correct value in column J.
=INDIRECT("J"&MATCH(B29,$B