Finding Smallest Date in Non Contiguous Range

  • Thread starter Thread starter Ryan
  • Start date Start date
R

Ryan

I have a lookup formula to find the smallest date in a column or more
specifically a range (SUB_102). The forumlua is correctly finding the
smallest value, however when it looks in the non contiguous range the
forumal errors out. I have the same problem if i define the cells
explicitly instead of using a range. I am using a non contiguous range
in order to exclude a summary cell throughout the column.

In the end this formula should find the smallest date in the range and
return the letter found in column A of the same row. Anyone know how
to make this work?

Thanks.

------SUB_102 range source------
=Worksheet!$U$2:$U$16,Worksheet!$U$18:$U$29

------Cell formula------
=LOOKUP(MIN(SUB_102),SUB_102,Worksheet!A:A)
 
did you try
= min(U2:u29)

provided the blanks are real blanks and not zeroes.

if a date cell is 0 it means it is a date 1/1/1900(in my version excel 2002 and configuration)
 
MIN works with non-contiguousranges but VLOOKUP does not.

you could use a UDF for this or amend your function

=IB(ISERROR(match(MYMIN,range1,false)), _
VLOOKUP(MyMin,Range2,thiscol,false), _
VLOOKUP(MyMin,Range1,thiscol,false))

so check range1, if its an error, val must be range2...so use the VLOOKUP
for range2 if error is true for range1, else use range1
 
MIN works with non-contiguousranges but VLOOKUP does not.

you could use a UDF for this or amend your function

=IB(ISERROR(match(MYMIN,range1,false)), _
    VLOOKUP(MyMin,Range2,thiscol,false), _
    VLOOKUP(MyMin,Range1,thiscol,false))

so check range1, if its an error, val must be range2...so use the VLOOKUP
for range2 if error is true for range1, else use range1
Click to expand...

Thanks Patrick,

I have taken a bit of a different route. My new function is

=LOOKUP(MIN(O$2:O$58),O$2:O$58,$A$2:$A$58)

the only problem as before is that if I have a tie (2 dates that are
the same) I need the topmost row to be the one that is reported. I
have tried to weave the ADDRESS funtion into the one above but so far
have been unsuccessful. How can I use this LOOKUP function with the
ADDRESS?

Thanks Patrick!
 
Back
Top