Hi,
I have a problem with using index match function where one criteria will always be exact match and exact or closest value.
Capture.PNG
I want a function which returns me the value closest to zero (or zero in some cases) for every hour in a day (there is always a different number of rows for each hour). I have been trying with the index match function where one match criteria was for the closest value (number closest to zero) and other for finding the specific hour. The problem is, if I select the whole column of number and use MIN(ABS(column-0) for the match function, that value is always zero (because at least one hour has some value zero). And if other hours don't have the zeros in some rows, I get NA.. I need to define matching value for each hour separately somehow.. I hope you understand the problem. Attached is also an example in excel.
Regards,
Rok
Bookmarks