LARGE IF ==> Formula question. No Array formula

[Xceller]

Hi all,

I a large spreadsheet about 4000 row and 40 columns. So I cannot use array formulas to do this.

I have a range of Dates:
1/12/2011
2/25/2011
3/25/2011
4/25/2011
5/16/2011
6/17/2011
7/19/2011
8/18/2011
9/23/2011
10/4/2011
11/4/2011
12/3/2011

I would like to compare these dates from the list to 5/15/2011 for example, and pick out the the closest date (4/25/2011). How do I achieve this without an array formula?

 

[T. Valko]

Hi all,

I a large spreadsheet about 4000 row and 40 columns. So I cannot use array formulas to do this.

I have a range of Dates:
1/12/2011
2/25/2011
3/25/2011
4/25/2011
5/16/2011
6/17/2011
7/19/2011
8/18/2011
9/23/2011
10/4/2011
11/4/2011
12/3/2011

I would like to compare these dates from the list to 5/15/2011 for example, and pick out the the closest date (4/25/2011). How do I achieve this without an array formula?

Wouldn't the closest date be 5/16/2011 or do you want the closest date that is less than or equal to the lookup date?

As long as the dates are in ascending order...

Book1
	A	B	C
2	1/12/2011	_	5/15/2011
3	2/25/2011	_	_
4	3/25/2011	_	4/25/2011
5	4/25/2011	_	_
6	5/16/2011	_	_
7	6/17/2011	_	_
8	7/19/2011	_	_
9	8/18/2011	_	_
10	9/23/2011	_	_
11	10/4/2011	_	_
12	11/4/2011	_	_
13	12/3/2011	_	_
Sheet1

This formula entered in C4:

=INDEX(A2:A13,MATCH(C2,A2:A13))

 

[Xceller]

Thank you, T. Valko. That iss elegant.
By the way, is it possible to use the SUMPRODUCT construct to do this, something like,
=LARGE(((A2:A13<C2)*(A2:A13)),1)




<c2)*(a2:a13))*1)
<c2)*(a2:a13)),1)
<c2)*(a2:a13))*1) .="" i="" am="" not="" able="" to="" do="" it,="" but="" don't="" know="" why.
<c2)*(a2:a13))*1)
Thanks again for your help!</c2)*(a2:a13))*1)
</c2)*(a2:a13))*1)></c2)*(a2:a13)),1)
</c2)*(a2:a13))*1)

 

[T. Valko]

Thank you, T. Valko. That iss elegant.

You're welcome!

By the way, is it possible to use the SUMPRODUCT construct to do this, something like,

=LARGE(((A2:A13<?XML:NAMESPACE PREFIX = C2)*(A2 /><C2)*(A2:A13)),1)< p> <c2)*(a2:a13))*1)< p><c2)*(a2:a13)),1)< p><c2)*(a2:a13))*1) do="" p know="" don?t="" but="" it,="" to="" able="" not="" am="" i="" .="" why.<><c2)*(a2:a13))*1)< p></c2)*(a2:a13))*1)<></c2)*(a2:a13))*1)></c2)*(a2:a13)),1)<></c2)*(a2:a13))*1)<>
<c2)*(a2:a13))*1)< p><c2)*(a2:a13)),1)< p><c2)*(a2:a13))*1) do="" p know="" don?t="" but="" it,="" to="" able="" not="" am="" i="" .="" why.<><c2)*(a2:a13))*1)< p>Thanks again for your help!

</c2)*(a2:a13))*1)<></c2)*(a2:a13))*1)></c2)*(a2:a13)),1)<></c2)*(a2:a13))*1)<>
<c2)*(a2:a13))*1)< p><c2)*(a2:a13)),1)< p><c2)*(a2:a13))*1) do="" p know="" don?t="" but="" it,="" to="" able="" not="" am="" i="" .="" why.<><c2)*(a2:a13))*1)< p>Like this:</c2)*(a2:a13))*1)<></c2)*(a2:a13))*1)></c2)*(a2:a13)),1)<></c2)*(a2:a13))*1)<>
<c2)*(a2:a13))*1)< p><c2)*(a2:a13)),1)< p><c2)*(a2:a13))*1) do="" p know="" don?t="" but="" it,="" to="" able="" not="" am="" i="" .="" why.<><c2)*(a2:a13))*1)< p></c2)*(a2:a13))*1)<></c2)*(a2:a13))*1)></c2)*(a2:a13)),1)<></c2)*(a2:a13))*1)<>
<c2)*(a2:a13))*1)< p><c2)*(a2:a13)),1)< p><c2)*(a2:a13))*1) do="" p know="" don?t="" but="" it,="" to="" able="" not="" am="" i="" .="" why.<><c2)*(a2:a13))*1)< p></c2)*(a2:a13))*1)<></c2)*(a2:a13))*1)></c2)*(a2:a13)),1)<></c2)*(a2:a13))*1)<>
<c2)*(a2:a13))*1)< p><c2)*(a2:a13)),1)< p><c2)*(a2:a13))*1) do="" p know="" don?t="" but="" it,="" to="" able="" not="" am="" i="" .="" why.<><c2)*(a2:a13))*1)< p></c2)*(a2:a13))*1)<>
</c2)*(a2:a13))*1)></c2)*(a2:a13)),1)<>=SUMPRODUCT(MAX((A2:A13<=C2)*A2:A13))

Format as Date
</c2)*(a2:a13))*1)<></C2)*(A2:A13)),1)<>

 

[Xceller]

Thanks again for the formula. It works perfect for me. Is it possible to pick up the 1st, 2nd, 3rd... largest value, such as 4/25/11, 3/25/11, 2/25/11 ?

 

[T. Valko]

Thanks again for the formula. It works perfect for me. Is it possible to pick up the 1st, 2nd, 3rd... largest value, such as 4/25/11, 3/25/11, 2/25/11 ?

You can use the INDEX formula and just subtract 1 for each nth instance you want.

For example:

Book1
	A	B	C
2	1/12/2011	_	5/15/2011
3	2/25/2011	_	_
4	3/25/2011	_	4/25/2011
5	4/25/2011	_	3/25/2011
6	5/16/2011	_	2/25/2011
7	6/17/2011	_	1/12/2011
8	7/19/2011	_	_
9	8/18/2011	_	_
10	9/23/2011	_	_
11	10/4/2011	_	_
12	11/4/2011	_	_
13	12/3/2011	_	_
Sheet1

This formula entered in C4 and copied down:

=INDEX(A$2:A$13,MATCH(C$2,A$2:A$13)-(ROWS(C$4:C4)-1))