# Error in Excel with Time function within Match function



## Dlark (Dec 27, 2022)

I am trying to use the Match function to return the row of an indicated table that a certain time value is on. The time is in mm:ss format on the table, so I want users to input the desired time to match as text for their simplicity (with data validation to ensure its correct format), and then use the Time function within the Match function to convert the input to match the format of the table for comparison. However, when using the Time function, the Match function returns the incorrect row, one row number short of what it should be to be precise. I attempted to do some debugging (shown below) and looked into the documentation of both the Time and Match functions, but can't figure out why this would happen. Is there something about the Time function I'm missing?

 Here is a breakdown of what I'm using and what I've done to debug and figure out it's the Time function that's causing me issues. Column B has the functions I've been using and their results, and Column C has direct links to the table to show what the output should be. Column D shows that the time values are exactly the same but that using them yields different results in the Match function. Column E is the user input time in text format, and the lookup array is in column G.

Time Function Problem.xlsxABCDEFG1CalculatedExpectedCalculated = ExpectedEntered TimeLookup Array2Time Equation=TIME(0,LEFT($E$2,2),RIGHT($E$2,2))=Sheet1!G1101:2300:003Time12:01:23 AM12:01:23 AMTRUE4Time Value0.000960648148148148000000.00096064814814814800000TRUE01:175Match Result910FALSE6Match Equation=MATCH(B3,G2:G19,1)=MATCH(C3,G2:G19,1)01:187801:1991001:201101:231201:261301:301401:331501:361601:391701:431801:461901:49Sheet1Cell FormulasRangeFormulaB3B3=TIME(0,LEFT($E$2,2),RIGHT($E$2,2))C3C3=Sheet1!G11D3:D5D3=B3=C3B4:C4B4=B3B5B5=MATCH(B3,G2:G19,1)C5C5=MATCH(C3,G2:G19,1)Cells with Data ValidationCellAllowCriteriaB2Text length=5E2Text length=5


----------



## GraH (Dec 28, 2022)

You have `MATCH(B3,G2:G19,1)` as formula, so it will return the position in the range `G2:G19`.  And 9 is correct.  That's the value in 10th row, but it is the 9th row in the given range.
If you want the row number, either change the range to`G1:F19` or add 1 to the formula used.


----------



## Dlark (Dec 28, 2022)

GraH said:


> You have `MATCH(B3,G2:G19,1)` as formula, so it will return the position in the range `G2:G19`.  And 9 is correct.  That's the value in 10th row, but it is the 9th row in the given range.
> If you want the row number, either change the range to`G1:F19` or add 1 to the formula used.


But the value in B3 is 01:23, which is in G11, the 10th row of the given range, but it returns saying it is the 9th row of the range.


----------

