Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Smallest Date in Non Contiguous Range
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Smallest Date in Non Contiguous Range
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" wrote: 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) . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Finding Smallest Date in Non Contiguous Range
On Nov 2, 5:02*am, Patrick Molloy
wrote: 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" wrote: 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) . 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding 2nd smallest number in range | Excel Worksheet Functions | |||
Finding last column in non-contiguous range | Excel Worksheet Functions | |||
Finding the last row of a contiguous range | Excel Programming | |||
Finding Smallest Value | Excel Discussion (Misc queries) | |||
formula to look up and return smallest date from a range of dates | Excel Worksheet Functions |