Would Like to Format Cells to Pull Data Based on Date Using VLOOKUP or IF (requires moving over columns)

[lltrs186]

Hello!

I have a spreadsheet that I'm using with three different sheets (Sheet1, Sheet2, Sheet3). In Sheet 1 I have a dropdown list (C4) that contains 20 types of licenses. When a license is clicked, cells c2:c4 will autopopulate with what type of test the person needs for the license (there can be as little as 1 test and as many as 4). Cells d4:F4 contain recommended test-by dates. Those recommended test by dates are housed in Sheet3. A2:16 are filled with the different tests and as of now B:I are filled with test dates. In Sheet 3 cell A1= today's date.

Obviously what happens with any testing dates is at some point the date is past. Right now my first testing date is 5/21, but after 5/21 I don't want that data to be filled in. What I'm trying to figure out is how to either use a VLOOKUP or IF statement based on today's date, so, for example, if today's date is 6/2/2016, then I want cell D2 (in sheet 1) to pull from D2 (sheet 3) and for E2 (in sheet 1) to pull from E2 (sheet 3).

I feel confused typing this out so I'll try and give more visual information:

Sheet 1:

C	D	E	F
2: Test selection	Test Dates	Test Dates	Test Dates
3: Test 1	Date 1	Date 2	Date 3
4: Test 2	Date 1	Date 2	Date 3
5: Test 3	Date 1	Date 2	Date 3
6: Test 4	Date 1	Date 2	Date 3

<tbody>
</tbody>

--------------------------------------------------------------------------------------------------------------------

A	B	C	D	E	F	...	I
A1: Today's Date
A2: Test Name	5/21/16	6/4/16	6/18/16	7/2/16	7/16/16
A3: Test Name	5/21/16	6/18/16	7/2/16	7/16/16
....
A15: Test Name	6/18/16	7/2/16	7/16/16

<tbody>
</tbody>

I've used two different formulas for pulling the data from Sheet 3 to Sheet 1:
1. =IFERROR(VLOOKUP($C$5,Sheet3!$A:$I,2,FALSE)," ")
2. =IF(Sheet3!$A$1<sheet3!b2,sheet3!b2,sheet3!c2)

The problem I have with both is that I can't figure out how to format it so that if it's past a certain date, then it automatically pulls from the next column over (and thus E2 & F2 would also pull from the next next column over).

Any help would be GREATLY appreciated!

</sheet3!b2,sheet3!b2,sheet3!c2)

 

[oldbrewer]

today's date

28/04/2016

dates

available

test1

01/02/2016

04/03/2016

06/05/2016

01/06/2016

fred needs a date for test1

first available date =

06/05/2016

formula giving the date

=IF(OFFSET($D$18,0,1)>$J$8,OFFSET($D$18,0,1),IF(OFFSET($D$18,0,2)>$J$8,OFFSET($D$18,0,2),IF(OFFSET($D$18,0,3)>$J$8,OFFSET($D$18,0,3),OFFSET($D$18,0,4))))

don’t know what you want to do maybe you can explain further in terms of this example

<colgroup><col span="4"><col span="4"><col><col><col span="12"></colgroup><tbody>
</tbody>