Finding Smallest Date in Non Contiguous Range

[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)

 

[Patrick Molloy]

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

 

[Ryan]

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

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!