Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 115
  • Last Modified:

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
0
gacto
Asked:
gacto
1 Solution
 
SimonCommented:
Please see attached with the formulae for each year added below the original data.
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)=A29,$B$1:$B$24))

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$1:$B$24,0))
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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)
0
 
barry houdiniCommented:
You can use a relatively simple LOOKUP formula, e.g. if you put the years in L2 down then you can use this formula in M2 copied down

=LOOKUP(DATE(L2,12,31),B$1:J$24)

By looking up the last day of the year in a sorted list of dates you will always get the latest date in that year, and the LOOKUP finds the corresponding value in column J

see attached

Edit: Hey Qlemo! Sorry, didn't see your reply when I replied, our approaches are very similar.....!

regards, barry
Lookup-dates.xlsx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now