Match function not working on filtered data using "FILTER" function (Solved)

[Kamalakkannan Raju]

Hello all,

In the attached excel sheet, column "L" of Summary sheet, based on time i am trying to lookup the position of the ISIN in Col. E of the Summary sheet in the data given in Dump sheet.

When doing so, amongst the 9 line items, the formula is working for 6 of them but for the other 3 rows, its returning #N/A error. But the surprising part is, when i go to the formula bar and hit F9 separately for those giving errors, for Filter function and match function, it shows that the position of the ISIN( In case of "IN2020220124", its 1). Then how come the its throwing error when i drag and drop the formula across the entire column.

PS: The data in the Dump sheet is sorted time wise and ISIN wise. Also, if you see the filter and match combo is working fine in case of col. O to col. R in the summary sheet.

Please someone guide me as to why #N/A error is coming in only in a few places.

Thanks again

 

[pecoflyer]

Your problem is due to https://learn.microsoft.com/en-US/o...l/floating-point-arithmetic-inaccurate-result
As you are comparing times there are sometimes discrepancies between values due to this phenomenon as there are many digits after the comma ( time is only a number to Excel.e.g. 1 hr = 1/24, 30 min = 1/48, etc..)
The ROUND function, applied to your formula, and adding a rounded time value column to your raw data solved the problem.
BTW, using entire column references is bad practice. Using Excel Tables is much better

 

[Kamalakkannan Raju]

Thank you so much pecoflyer.

I implemented the solution that you suggested and it worked fine.

Shall keep in mind about using full column references. Thanks again.

 

[pecoflyer]

Most welcome