# Solved: excel 2013 "conditional range"

Discussion in 'Business Applications' started by andrew1968, Jul 3, 2014.

Not open for further replies.
1. ### andrew1968Thread Starter

Joined:
Jul 3, 2014
Messages:
11
I have a series of sequential dates in column A
In column B I have numerical data
In column C I would like to look at the previous year as determined by column A and the result would be the highest number in that range from column B.

The period in column A I'm looking at constantly changes as I move down the spreadsheet because the dates change. I think I may use the LOOKUP OR VLOOKUP function to get my result after defining the range.

Can you help me define the range for the lookup as a formula. (i.e A255:A255- one year)

Thanks! I hope this is clear.

2. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
perhaps you could post a sample spreadsheet with the data - include the expected results - so we can see what you are trying to achieve

3. ### andrew1968Thread Starter

Joined:
Jul 3, 2014
Messages:
11
Here is a sample file of what I'm looking for. Thanks everyone! This is my first time here and if I get an answer that would be great!!!!

File size:
14.4 KB
Views:
51
4. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
what range would you use

=VLOOKUP(A253-365,\$A\$1:\$B\$252,2,FALSE) looks up the date
Use
=VLOOKUP(A253-365,\$A\$1:\$B\$252,2,TRUE) to get the nearest lowest date (as not all dates are in the list

But what is the max you are looking for ?

5. ### andrew1968Thread Starter

Joined:
Jul 3, 2014
Messages:
11
So I'm not wanting to look up a date.

For example whichever line I'm on I will go to column A and see what date that is. That will be the end date of my search the beginning date of my search will be one year prior to that (going up column A until I get to one year earlier). Once I have the date range established then I will look in that same range in column B for the MAX number.

does that make sense?

6. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
I think this will work

I entered into column C253 and copied down

7. ### andrew1968Thread Starter

Joined:
Jul 3, 2014
Messages:
11
Thank you... I just made one small change it it was perfect.

I wish I knew excel that well. Any suggestions on how to learn more of these advanced formulas?

Anyway, I'm supper happy THANK YOU!!!!!!

8. ### etafModerator

Joined:
Oct 2, 2003
Messages:
65,252
First Name:
Wayne
there maybe a better way to do it.

i just answer questions in some of the excel forums and then research the question to see how to solve

9. ### Zack Barresse

Joined:
Jul 25, 2004
Messages:
5,452
If I understood you correctly, you could also us this array formula, as an alternative to using the INDIRECT function (I just generally try to stay away from it, since it's volatile and expensive, in terms of recalc time)...

Code:
`=IFERROR(MAX(IF((A\$1:A1>=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1)))*(A\$1:A1<=A1-1)*(MIN(A\$1:A1)<=DATE(YEAR(A1)-1,MONTH(A1),DAY(A1))),B\$1:B1)),"")`
The formula would be entered into cell C1 and confirmed with CTRL+SHIFT+ENTER instead of just ENTER, since it's an array formula. Copy down.

This does get an offset result of the previously posted formula, in that it looks for everything before the date in the row. So it will be in the previous year (rolling) not including the date of the cell in column A.

I haven't run any speed tests, but I'd wager a guess that the array formula would probably beat the other in terms of recalc over several iterations. A 101 ways to skin the proverbial Excel cat.

HTH

As Seen On

### Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

over 733,556 other people just like you!