Searching Columns

A

Aviator

I have a box (cell) at the top of the page in which I want it to search a
column for the latest date. Once it finds the latest date it takes the data
from a cell in the same row as the latest date. Can this be done? If so, how?

Aviator
 
D

Dave Peterson

You can use a formula like:
=MAX(C:C)
to find the biggest date in column C (if column C contains nothing but dates)

To return the value in the column D that matches the biggest date:
=INDEX(D:D,MATCH(MAX(C:C),C:C,0))

Debra Dalgleish has some nice notes for =index(match()) at:
http://www.contextures.com/xlFunctions02.html
 
A

Aviator

This worked great! Thank you.

I have three additional similar questions.
1. Can you do the same thing but if there is no values have it left blank or
return Open? (Currently, it shows #NA)

2. Can you do the same thing except just include odd numbered rows? (ie. D3,
D5.... D589)?

3. Can you do the same thing except instead of the biggest date have the
second biggest date?

Thank you so much, I have al of these scenerios, and it's driving me crazy.

Thanks,

Aviator.
 
D

Dave Peterson

#1. You can check the portion of the formula that returns the error. In this
case, it's the match() portion. Then have your formula do nice things:

=IF(ISERROR(MATCH(MAX(C:C),C:C,0)),"",INDEX(D:D,MATCH(MAX(C:C),C:C,0)))

#2. I can't! But that doesn't mean that it's not able to be done. If you
don't get any help in .misc, you may want to head over to .worksheet.functions
and post there. The people who spend lots of time in that newsgroup have their
head on weird and can do miraculous stuff with formulas.

#3. The worksheet funtion =max(c:c) returns the largest in a list of numbers.
There's another worksheet function that can find the nth largest in a list.
=large(range,N)

=IF(ISERROR(MATCH(LARGE(C:C,2),C:C,0)),"",INDEX(D:D,MATCH(LARGE(C:C,2),C:C,0)))

One thing to worry about (needlessly???) is that if the largest and the second
largest dates are the same. Then the match will find the first in the list--no
matter which one you're looking for--and return the cell adjacent.
 
Top