Thread: Currency Rate between From To date

Hi all,
I have currency rate table using between date From (col: K), To (L), Currency (M), Rate (N).
FROM TO CUR RATE
1/3/2011 1/9/2011 USD 9,142.51
1/10/2011 1/16/2011 AUD 8,950.71
1/17/2011 1/23/2011 AUD 9,027.31

I want to lookup this rate in exact date and currency into other table.
Date (A) Currency (B) Rate (C)
1/3/2011 USD [formula] = 9,142.51
1/4/2011 USD [formula] = #N/A
1/5/2011 USD [formula] = #N/A
1/10/2011 USD [formula] = 8,950.71

[formula] =INDEX($N$4:$N$30,MATCH(1,($K$4:$K$30=A4)*($M$4:$M$30=B4),0))

Why the result for date in between from to always #N/A. Please advice. Thanks.

Originally Posted by zbor

Within XL 2007 you can use SUMIFS function:

=D4*SUMIFS($L$4:$L$1000,$I$4:$I$1000,"<="&A4,$J$4:$J$1000,">"&A4,$K$4:$K$1000,B4)

Thanks, it works in 2007.

But I will save in xls 2003 format with additional macro (not xlsm).
Any idea using index formula?