Excel Lookup function

[Cgeisler]

Hello, I am looking to derive a value based on a variable data set. I want take the date that is chosen and find that value in Column C then look to Column E's # that is chosen and give me the value from Column C that is that # (in this case 1) day prior to the Date I entered. Another kicker is that it cant be a weekend, so it would need to skip over all dates that in column D are labeled 1 or 7. For this example, the value the formula should have chosen is 1/9/15. Any ideas? Thank you

Date:	01/12/2015
Column A	Column B		Column C	Column D		Column E
				1= Sunday, 2=Monday, 3=Tuesday, 4=Wednesday, 5=Thursday, 6=Friday,7=Saturday		Days Prior
				Day of Week		1
9/7/2015	1/1/2015		1/1/2015	5
10/12/2015	1/2/2015		1/2/2015	6
11/11/2015	1/3/2015		1/3/2015	7
11/26/2015	1/4/2015		1/4/2015	1
12/25/2015	1/5/2015		1/5/2015	2
1/1/2016	1/6/2015		1/6/2015	3
1/18/2016	1/7/2015		1/7/2015	4
2/15/2016	1/8/2015		1/8/2015	5
5/30/2016	1/9/2015		1/9/2015	6
7/4/2016	1/10/2015		1/10/2015	7
9/5/2016	1/11/2015		1/11/2015	1
10/10/2016	1/12/2015		1/12/2015	2
11/11/2016	1/13/2015		1/13/2015	3
11/24/2016	1/14/2015		1/14/2015	4
12/26/2016	1/15/2015		1/15/2015	5
1/2/2017	1/16/2015		1/16/2015	6
1/16/2017	1/17/2015		1/17/2015	7
2/20/2017	1/18/2015		1/18/2015	1
5/29/2017	1/19/2015		1/19/2015	2
7/4/2017	1/20/2015		1/20/2015	3
9/4/2017	1/21/2015		1/21/2015	4
10/9/2017	1/22/2015		1/22/2015	5
11/23/2017	1/23/2015		1/23/2015	6
12/25/2017	1/24/2015		1/24/2015	7
1/1/2018	1/25/2015		1/25/2015	1
1/15/2018	1/26/2015		1/26/2015	2
2/19/2018	1/27/2015		1/27/2015	3
5/28/2018	1/28/2015		1/28/2015	4
7/4/2018	1/29/2015		1/29/2015	5
9/3/2018	1/30/2015		1/30/2015	6
10/8/2018	1/31/2015		1/31/2015	7
11/12/2018	2/1/2015		2/1/2015	1
11/22/2018	2/2/2015		2/2/2015	2
12/25/2018	2/3/2015		2/3/2015	3
1/1/2019	2/4/2015		2/4/2015	4
1/21/2019	2/5/2015		2/5/2015	5
2/18/2019	2/6/2015		2/6/2015	6
5/27/2019	2/7/2015		2/7/2015	7
7/4/2019	2/8/2015		2/8/2015	1
9/2/2019	2/9/2015		2/9/2015	2
10/14/2019	2/10/2015		2/10/2015	3
11/11/2019	2/11/2015		2/11/2015	4
11/28/2019	2/12/2015		2/12/2015	5
12/25/2019	2/13/2015		2/13/2015	6
1/1/2020	2/14/2015		2/14/2015	7
1/20/2020	2/15/2015		2/15/2015	1
2/17/2020	2/16/2015		2/16/2015	2
5/25/2020	2/17/2015		2/17/2015	3

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[cbatrody]

Hi,

I could do this after adding a helper column:

You have dates in Column A, B & C. Column D contains numbers.


Add a new helper Column E, In E2:
=ROW(C2)
drag this down till the last cell in the data range


Lookup value in H7: 01/12/2015
try the following formula in H8:

=INDEX(C$1:C$49,LARGE(IF((E$1:E$49<MATCH($H$7,C$1:C$49,0))*(D$1:D$49<>1)*(D$1:D$49<>7),ROW($C$1:$C$49)),ROW($A$1)))

=INDEX(C$1:C$49,LARGE(IF((E$1:E$49<MATCH($H$7,C$1:C$49,0))*(D$1:D$49<>1)*(D$1:D$49<>7),ROW($C$1:$C$49)),ROW($A$1)))

This needs to be array entered (to be confirmed by pressing CTRL+SHIFT+ENTER)

PM me your e-mail address if you want the sample file that I created for you!

 

[cbatrody]

For some strange reasons I guess the system is auto correcting my formula. The actual formula is different than what I posted in the above thread. I did it twice - once with code tags and again without the code tags. Strange!!

 

[Cgeisler]

OK, tweaked the above formula provided and it works now. Thank you for your help CBatrody. The formula that worked is as follows:

INDEX(D$11:D$27768,SMALL(IF((E$11:E$27768>MATCH($D$4,D$11:D$27768,0))*(F$11:F$27768<>1)*(F$11:F$27768<>7),ROW($D$11:$D$27768)-10),$B6))