How to write formula that searches for a populated previous date if the date specifed doesn't exist in a data set?

[Dawson14]

Hey all, long time lurker, first time poster.

I'm currently having trouble finding a way to write a formula that v-vlookups a specified date and returns a value but also return the closest previous date if the specified date doesn't exist in the data set. I have successfully written a formula that can do this, but there are situations where I would need to nest 10+ IF statements to essentially cover all possibilities. Let me explain:

I am trying to build a very basic order growth predictor for the current sales year based on the previous 5 years of sales data. I want to be able to specify a date range in the top left corner of the file, and then populate the table with data by team based on the date range and year. I have data pulled by sales team and cumulative orders by day in another tab. Essentially the question I am trying to answer is "Hey, how much did orders grow between these two dates for this year range?". The issue I'm having is that you would think our data records would have 365 individual records for each year per sales team(that's how many days in a year...duh!), but if there was no order changes for a certain day, the day doesn't exist.

So say if I wanted to look up what the orders were on March 27th, and June 1st for Team A in 2013-2014.

Start Year	2013
End Year	2013

<tbody>
</tbody>

	Month	Day
Start	03	27
End	06	01

<tbody>
</tbody>

Here is the table that gets populated

		2013			2014
Region	Team	27-Mar	1-Jun	% Growth	27-Mar	1-Jun	% Growth
Pacific	A	328,084	328,294	.1%	254,594	260,259	2.2%
Pacific	B	165,114	163,813	-.8%	188,442	N/A	N/A
Pacific	C	325,289	320,751	-1.4%	270,255	260,208	-3.7%

<tbody>
</tbody>

As you can see, the record for June 1st 2014 in Team B doesn't exist so it gives back an N/A. Below you can see the table set where June 1st is missing along with May 30th. What I want to do is have a formula that can recognize that the date specified doesn't exist and then look back in history for the next date

Team	Day	Order Change
Team B	5/30/2013	2
Team B	6/02/2013	140
Team B	6/03/2013	-191

<tbody>
</tbody>

Here is my current formula:

=IFERROR(IF($G$14<$B$2,"",VLOOKUP(CONCATENATE($C17,$H$15),Pivot!$A:$H,8,FALSE)),IF($G$14<$B$2,"",VLOOKUP(CONCATENATE($C17,$H$15-1),Pivot!$A:$H,8,FALSE)))

As you can see my current formula has an IFERROR that is essentially stating "If you get an error because the date doesn't exist, go back one day and grab that one". Since I have a two day gap it doesn't work. Now like I said in the beginning of the post, I could nest "IF" formulas all day long until I basically cover the largest data gap I have and call it a day. But since I like to learn new things and do things right, I wanted to see if there is anyway to CORRECTLY write this to take care of it. Any thoughts?

 

[dave2018]

That is certainly possible (and kind of prefered, rather that nesting 20 IF statements). but can you kindly upload a spreadsheet with some real-life sample data (no personal info, obviously) so i can see how your data is arranged, and type in "simulated" values in the cells u want the formula to be in, and then maybe color those red, so i can change that into a working, live formula.

u can upload to google, or dropbox and reply with a link.

Glad to (try to) help.