Need to find closest Date and rate based another criteria

[samorita]

Hello Friends,
I have three columns, i.e. Column-A & B &C. Now I need to find the closest or equal date and respective rate in different columns, i.e. Column-E & F based on the criteria set in Column-D Column -G. I have used =MAX(IF(A1:A100<=E3,A1:A100)) array formula to find the closest date but I can not proceed further. Please help me to find a way so that I can finish my job in a better way. Now I do it manually.

Col-A Col-B Col-C Col-D Col-E Col-F Col-G
Date Duration Rate Rate Closest date Date
27-Apr-15 182 7.81 30 5.25% 30-Apr-15 5-May-15
20-Apr-15 364 8.21 91 7.27% 4-May-15
30-Apr-15 30 5.25 182 7.65% 4-May-15
27-Apr-15 91 7.33 364 8.21% 20-Apr-15
4-May-15 91 7.27
4-May-15 182 7.65
5-Apr-15 364 8.05
11-May-15 91 6.99
11-May-15 182 7.33
13-May-15 30 5.25
18-May-15 91 6.58
18-May-15 364 7.48
19-May-15 30 5.25
25-May-15 91 5.75
25-May-15 182 6.5
27-May-15 30 5.25

With Thanks,
Samorita

[bsuperiorsystem]

please attach the example file

[newdoverman]

The way that I understand your problem you are wanting to match the values in columns D & F against columns A & B and return values in column C into column E. Column G is a mystery to me. I don't know what you mean by "closest" match as all your examples are exact matches. In addition, closest matches require data to be in order which yours is not.
This array formula will return an exact match for combined values in columns D and F against combined values in B and A.

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

[samorita]

Dear bsuperiorsystem,

With the help of the formula of dear Mr. newdoverman, I have solved one requirement. But I can not find the date. I am attaching a sample excel file (2007 version) using the mentioned formula. I have added necessary comments in the file. Still if you need any other clarification, I will do so. Please have a look on the file and help me. I will be highly grateful.

With thanks,
Samorita

[newdoverman]

I have no idea what those "base dates" are and how they relate to your data. If you want to find "nearest dates" to some date then the search usually requires that the data being searched be in date order which yours is not.