Index match with two criteria, one closest value and one specific

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

Hi,

I was at home when I posted the first post, so I didn't have the excel file and I have just created an example. Attached is now the real excel file with formulas and everything.

http://s000.tinyupload.com/?file_id=...67883359428115 (the file is too big for the forum)

Thank you for your help. Let me know if you need some additional explanation about the problem itself.

Rok

You are right.

Regards