Collating mutiple columns' data

I think you'd need to use SUMPRODUCT - please see attached.

This would not automatically populate the new table with currency and dates though, this would need to be completed manually or with a macro - all this will do is then take the currency and date and bring back an amount and rate for that day.

If you paste your current data into columns A-D, and you will also need to amend the SUMPRODUCT formula to look at the last line (easiest way to do this would be to highlight cols F-I and ctrl+H to replace '$7' with '$however many lines you want to look in'.

Hope that helps.

Thanks brokenbiscuits. I have a older version of excel so can't view it, but will have a look in to the sumproduct funtion. Thanks for your help.
B