0

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?

Ram
  • 3,092
  • 10
  • 40
  • 56
Ratafia
  • 97
  • 1
  • 1
  • 9
  • You should ask this question on [SuperUser](http://SuperUser.com) . StackOverFlow is a question and answer site for programmers, and questions related to programming. – user2140261 Oct 10 '13 at 21:04

2 Answers2

5

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

barry houdini
  • 45,615
  • 8
  • 63
  • 81
  • I've tried both of these formulae in the specific cell that's having an issue, and the results are a little strange. The first solution you've provided which replaces the comparison between the exception date for "ON HOLD" and the other dates in the index, sets the end value to "ON HOLD" despite there being nothing in that cell. The second iteration of the formula sets it instead to the earliest date in the range, which is also not going to work. What is the reason for replacing the cell reference to Y1 (today's date) with TRUE? – Ratafia Oct 11 '13 at 12:19
  • Apologies, you are right - my version was giving the position of the **first** value that's <= Today whereas you need the position of the **max** value that's <= Today - I'm revising my answer now...... – barry houdini Oct 11 '13 at 13:11
  • If at all possible it should be the max value being read from right to left. IE: Col A has 10/8, Col B has 10/10, Col C has 10/9, Col D has 10/10; should return Col D as a match. So far, every other instance of two identical dates matches it reading it from right to left, but due to some circumstance I don't understand rarely it will not follow that rule. Weirder still, the row that had the issue yesterday does not have the issue today. This is a shared file, and I'd prefer to avoid coding this solution with a VBA macro due to the high amount of traffic it gets, if at all possible. – Ratafia Oct 11 '13 at 14:00
  • That code is still returning "ON HOLD" without anything in column Q, meaning it should be generating an error somewhere along the line. – Ratafia Oct 11 '13 at 14:33
  • I have, however, found a reproduction for the error. Not all columns have values in them, and when the date entered IS equal to today's date, and has at least one empty column between them, it takes the first date equal to today's reading left to right. IE: Col A has Oct 8, Col B has Oct 11, Col C is empty, Col D has Oct 11. This returns Col B. The way I'm seeing it, I made an error in writing this in thinking it would continue with empty spaces, yes? – Ratafia Oct 11 '13 at 14:39
  • OK, I edited my answer again - the last formula shown should do what you want - no matter the order of the dates. Your original may work.......but if the dates are not in ascending order it may not....and spaces in there will mess it up too – barry houdini Oct 11 '13 at 14:45
  • Ok, your formula works as described, but I need to be able to account for empty spaces. After researching array formulas for a short while, I've written a different formula that will return the column number in which it is the maximum value and the number of maximum values further in the array is 0, allowing it to account for empty space. `=IF(AND(COUNTIF(S10:$X10,MAX($R10:$X10))=0,R10:X10=MAX(R10:$X10),COUNTIF(R10:$X10,MAX($R10:$X10))),COLUMN(),"No")` However, in order to return it as a column value, it needs to be present in each column. Is there a way to iterate it for each row in one cell? – Ratafia Oct 11 '13 at 19:22
  • Considering that your answer will work for cases that do not have empty entries in the range being used, I will accept your answer, as it is the closest towards solving this issue. – Ratafia Oct 14 '13 at 12:47
1

Omitting the [match_type] from the syntax MATCH(lookup_value, lookup_array, [match_type]) may lead to unexpected results when the lookup_array is not sorted.

http://office.microsoft.com/en-gb/excel-help/match-function-HP010062414.aspx

pnuts
  • 58,317
  • 11
  • 87
  • 139