MSGROUPS.NET | Search | Post Question | Groups | Stream | About | Register

Multiple lookup between two dates

• Follow

```Hi,
I have a ratesheet in excel like e.g.

Carrier FromDate  ToDate        Rate
A          01.02.10.  28.02.10.   100
A          01.01.10.  14.01.10.   99
A          15.01.10.  31.01.10.   98
B           13.01.10. 31.10.10.   101
C          16.01.10.  21.02.10.   97
A          13.12.09.  28.12.09.   101
etc.

The date for respective carrier are not overlapping. So in the rate sheet
will never happen, that a rate is valid for carrier A fm 01.01.10. till
31.01.10. and another rate for the same carrier A fm 15.01.10. till 31.01.10.
In such case the previous one will be valid just till 14.01.10.

I need a formula if e.g. I wd like to find what rate was valid on 18.01.10.
for carrier A (in this example it wd be in third row the rate 98).

Tks for any help !
Ales

```
 0

```Assuming carrier is in cell E2, date is in F2, something like this:

=SUMPRODUCT(--(A2:A10=E2),--(B2:B10<=F2),--(C2:C10>=F2),D2:D10)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*

"Ales" wrote:

> Hi,
> I have a ratesheet in excel like e.g.
>
> Carrier FromDate  ToDate        Rate
> A          01.02.10.  28.02.10.   100
> A          01.01.10.  14.01.10.   99
> A          15.01.10.  31.01.10.   98
> B           13.01.10. 31.10.10.   101
> C          16.01.10.  21.02.10.   97
> A          13.12.09.  28.12.09.   101
> etc.
>
> The date for respective carrier are not overlapping. So in the rate sheet
> will never happen, that a rate is valid for carrier A fm 01.01.10. till
> 31.01.10. and another rate for the same carrier A fm 15.01.10. till 31.01.10.
> In such case the previous one will be valid just till 14.01.10.
>
> I need a formula if e.g. I wd like to find what rate was valid on 18.01.10.
> for carrier A (in this example it wd be in third row the rate 98).
>
> Tks for any help !
> Ales
>
```
 0

```tks Luke !!
Needed half day experimenting why the formula returns "N/A" and how to make
the formula if the worksheet has different rows (next month more) . The "N/A"
reason was that at the end of my 2000 rows in column A there were few "N/A"
which I did not see initially and which caused the formula to return "N/A".
When I deleted the "N/A"-rows works now. Tks a lot !

Ales
```
 0

2 Replies
753 Views

Similiar Articles:

8/1/2012 8:56:06 AM