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

# 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
1 Solution

Commented:
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

Batchelor, 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

Commented:
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.