Array Lookup to Find Closest Date and Next Closest Date

[bidness_n]

Googled all over and haven't been able to find a solution- thanks in
advance for any help!

In Col A I have a list of items that are due for arrival; in Col B I
have the corresponding arrival dates. List must remain unsorted
(sorted would be too easy!)

Col A Col B

Monkey 12/1/07
Cat 1/15/08
Cow 11/21/07
Monkey 11/18/07
Peanut 1/30/08
Monkey 12/24/07
Cow 11/14/07

In Col C I need a formula to drag down that will give the earliest
date available, so for Monkey the date 11/18/07 would fill in for
every instance of Monkey; Cow would get 11/14/07.

Is it possible to set column D with a formula that would give the

next< possible available date? So for Monkey, the answer would be

12/1/07?

Thanks again!

 

[Guest]

You don't need any formulas. Put a header cell on top of both column. Then
setup a pivot with animal in the row area and min of date in the data area.
You should get:

Min of date
animal Total
Cat 1/15/2008
Cow 11/14/2007
Monkey 11/18/2007
Peanut 1/30/2008

for your sample data

 

[Max]

Try in C1, array-entered with CTRL+SHIFT+ENTER:
=INDEX($B$1:$B$100,MATCH(SMALL(IF($A$1:$A$100=$A1,$B$1:$B$100),COLUMNS($A:A)),IF($A$1:$A$100=$A1,$B$1:$B$100),0))
Copy C1 to D1, fill down. Adapt the ranges to suit.

---

 

[Max]

Format cols C and D as dates

---

 

[Guest]

How about this array formula:

=MIN(IF(A$1:A$100=A1,B$1:B$100))

 

[bidness_n]

How about this array formula:

=MIN(IF(A$1:A$100=A1,B$1:B$100))

Thanks everyone!!!

 

[Max]

Welcome. Think my earlier suggestion was overdone. No need for the
INDEX/MATCH. Just the SMALL part would suffice here.

In C1, array-entered with CTRL+SHIFT+ENTER:
=SMALL(IF($A$1:$A$100=$A1,$B$1:$B$100),COLUMNS($A:A))
Format C1 as date. Copy C1 to D1, fill down. Adapt the ranges to suit.

 

[RagDyer]

You didn't respond to your post from yesterday!

It's best to stick to a single thread concerning a single subject.

http://tinyurl.com/2xexre