im using that formula in finding closest date, it is working.but i want to add condition: closest date that is less than 30 days of the current date (today).it is possible?
Anyone can help please?thank you
Supposing you have a list of numbers in a column, and now you are required to find out the closest or nearest value to a given value from the list of numbers. How do you deal with it? Actually, you can find closest value or nearest value in Excel with following steps.
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 70% of your time and solve 80% Excel problems for you. 300+ advanced features designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
For example, you have a list of numbers in Column A, and now you will find the closest value or the nearest value of 18 from the Column A. You can do it as follows:
1. Select a blank cell; in this case, we select the Cell D2, and enter the formula of =INDEX(A1:A20,MATCH(MIN(ABS(A1:A20-D1)),ABS(A1:A20-D1),0)) into the cell D2.
Note: In above formula, A1:A20 is the range that you want to find the specific value, and D1 is the searching value that you want to be compared with.
|Formula is too complicated to remember? Save the formula as an Auto Text entry for reusing with only one click in future!
Read more… Free trial
2. Press the Ctrl + Shift + Enter keys together to apply the formula. Then it displays the closest value to the given searching value. See the following screenshot:
Sometimes, you may want to find out and select all closet values to the given value in a range. Actually, we can define a deviation value, and then apply Kutools for Excel’s Select Special Cells utility to find out and select all closest values within the diviation range of give value easily.
Kutools for Excel - Combines more than 300 Advanced Functions and Tools for Microsoft Excel
For example, in our example we define the deviation value as 2, and the given value is 18. Therefore, we need to find out and select values between 16 (=18-2) and 20 (=18+2). Please view following steps:
1. Select the range where you will search for closest values to the give value, and then click Kutools > Select > Select Specific Cells.
2. In the opening Select Specific Cells dialog box, please:
(1) Check the Cell option in the Selection type section;
(2) In the Specific type section, click the first drop down list and select Greater than or equal to from it and type 16 into following box, and then select Less than or equal to from the second drop down list and type 20 into following box. See left screenshot:
3. Click the Ok button to apply this utility. Then a dialog box comes out and shows you how many cells have been selected. Just click the OK button to close it.
Then you will see all closest values within the deviation range of the given value are selected as below screenshot shown: