Using MATCH() function with an unsorted list
I'm having an issue with a spreadsheet formula that uses the MATCH() function.
=IFERROR(IF(LENB(Y2461)<> 0, "Complete", IF(LENB(Q2461)<> 0, IF(Q2461-$Y$1<MATCH($Y$1,R2461:X2461),"ON HOLD"), INDEX($R$4:$X$5,1,MATCH($Y$1,R2461:X2461)))),"ON HOLD")
This is the formula in the cell with the specific problem. I'm using match to look through a horizontal range of data and determine which column contains the most recent change, with exceptions for when the "Complete" column has a date, and the "ON HOLD" column has a date. Cell Y1 contains =TODAY(), so it checks each cell against today's date. The cells are formatted into dates, and are entered as dates from left to right. However, the entries can start again from an earlier column and leave the dates out of order.
The issue is, on only a few occurrences at a time, MATCH() will return a column that isn't the most recent. As far as I've been led to believe, and how it works for the other instances of this formula, is that it reads the array from right to left and stops at the first "highest" date.
The error is occurring on one row specifically, while the expected result works multiple times elsewhere on the sheet.
Am I using MATCH() wrong by assuming that it will read in a certain direction every time? Is there a different error in the code? Or is there a different way to get the result I've programmed it for?
Answer
MATCH with no third argument as you are using it is the same as MATCH with 1 or TRUE as third argument, which means that you can only guarantee that it will work OK if the range used - R2461:X2461 - is always in ascending order....but you say that isn't the case so I don't think you can guarantee the formula will work
Try using this version instead [revised as per comments]
=IFERROR(IF(LENB(Y2461)<> 0, "Complete", IF(LENB(Q2461)<> 0, IF(Q2461-$Y$1< MATCH(MAX(IF(R2461:X2461<=$Y$1,R2461:X2461)),R2461:X2461,0),"ON HOLD"), INDEX($R$4:$X$5,1,MATCH(MAX(IF(R2461:X2461<=$Y$1,R2461:X2461)),R2461:X2461,0)))),"ON HOLD")
confirmed with CTRL+SHIFT+ENTER
The logic of the alterations is that this revised MATCH part
=MATCH(MAX(IF(R2461:X2461<=$Y$1,R2461:X2461)),R2461:X2461,0)
will do the same as this part
=MATCH($Y$1,R2461:X2461)
whether R2461:X2461 is in ascending order or not - it finds the largest values which is <= to Y1 and gives you the position of the first instance.
Revised
If you want the rightmost date where the largest date <= today is duplicated then you can use this construction in place of MATCH
=MAX(IF(R2461:X2461<=$Y$1,COLUMN(R2461:X2461)-COLUMN(R2461)+1))
which would make the complete formula like this:
=IFERROR(IF(LENB(Y2461)<> 0, "Complete", IF(LENB(Q2461)<> 0, IF(Q2461-$Y$1< MAX(IF(R2461:X2461<=$Y$1,COLUMN(R2461:X2461)-COLUMN(R2461)+1)),"ON HOLD"), INDEX($R$4:$X$5,1,MAX(IF(R2461:X2461<=$Y$1,COLUMN(R2461:X2461)-COLUMN(R2461)+1))))),"ON HOLD")