How to find the closest match meeting conditions

[glenn37475474]

I have a reference table comprising a column of dates in ascending order and a column of readings (numbers in no order). Each date has a corresponding reading eg 5Apr11 = 1000. In my worksheet I have an input date, tolerance and number eg 1Apr11, 5, 1007.

For the inputted data, I would like to show the date corresponding to the closest number in the reference table that occurs after the input date and is within the +/- tolerance.

What formula would achieve this?

Thanks in advance for your reply.

[Haseeb Avarakkan]

Hello glenn, Welcome to the forum!

Would you please attach a sample file with expecting result?

Scroll down & click Go Advanced then down click on Manage Attachment, browse the file & upload it.

[DonkeyOte]

Welcome to the Board.

If we assume for sake of demo. that:

Dates are in Col A
Associated values are in Col B

Min Date is C1
Tolerance is D1
Number is E1

then to return the first date thereafter for which associated value is within tolerance of specified number:

Please Login or Register  to view this content.

Obviously modify ranges to meet own requirements but note that Arrays (array entry required as outlined) are inefficient and precedent ranges should be kept as lean as possible.

Note: I have assumed the tolerance to be valid in either direction (tolerance of 5 equates to -5 to +5)