Index/Match across multiple columns

Hi guys, hoping you can help me.

I have a spreadsheet with DATES organised in Columns, and a € value beside each date. There is a different column for each month, ie

A B C D
DATE € DATE €
1 01-Jan €0.00 01-Feb €408.02
2 02-Jan €0.00 02-Feb €604.24
3 03-Jan €1,733.00 03-Feb €742.12
4 04-Jan €2,534.00 04-Feb €0.00
5 05-Jan €3,705.00 05-Feb €0.00
6 06-Jan €0.00 06-Feb €395.00

and so on.

In cell B36, I have the following formula: =IF(A36="","",INDEX(B2:B32,MATCH(A36,A2:A32,0))), so that when I enter a date (from Column A) in A36, the corresponding € for that date is displayed in B36.

However, I'd like to be able to enter ANY date in A36 (not just from Column A) and have the corresponding € returned - I just can't figure out how to.

Can anyone help, please?

Thanks,

Brendan.

Hi Brendan,

Use SUMPRODUCT with an offset array combination such as:

Please Login or Register  to view this content.