Lookup based on a date

[mkvassh]

I have a table with dates and currency-rates. There is only one currency rate per month, but the day can be different since the date is the last working day of the month.

I want to lookup these rates based on a date where the day can be any day in the month. Any proposals?

Table example:

Date Rate
30.06.2003 7,09320
31.05.2003 6,70466

Lookup date: 20.05.2003

Regards,
Magne from Norway

[DonkeyOte]

If we assume your data is in A1:B3 (A1:B2 headers) and your lookup value is in say C1 then for the rate:

[deleted]

=INDEX($B$2:$B$3,MATCH(C1,$A$2:$A$3,-1))

There are other ways of course.... assumes criteria will never exceed last working day (ie > fx date for given month)

[mkvassh]

It works. Thank you :-)

[franciz]

May I know how does the last parameter -1 in the Match formula works

=INDEX($B$2:$B$3,MATCH(C1,$A$2:$A$3,-1))

TIA

[DonkeyOte]

Check out XL Help on the MATCH function.