Solved

Excel Formula

Posted on 2015-01-17
3
101 Views
Last Modified: 2015-01-17
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
Comment
Question by:gacto
3 Comments
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40555273
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
 
LVL 68

Expert Comment

by:Qlemo
ID: 40555303
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
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 40555305
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now