date specific, from multiple rows and columns

Hello:


Attached is the file in which "sheet 1" tab indicates the criteria which need result from values in "Mpspreads" tab.


Note: Event date can be unique and formula must find result from "start date" and "end date".

I appreciate any guidance/advice on how to do this? Thanks.

Hi

You didnt explain much but if I understand you correct then in D2 and down:

=IF(ISNA(IFERROR(INDEX(Mpspreads!$A$1:$BM$55,MATCH(A12&B12,Mpspreads!A:A&Mpspreads!C:C,0),MATCH(Sheet1!C12,Mpspreads!$1:$1,0)),INDEX(Mpspreads!$A$1:$BM$55,MATCH(A12&B12,Mpspreads!B:B&Mpspreads!C:C,0),MATCH(Sheet1!C12,Mpspreads!$1:$1,0)))),"",IFERROR(INDEX(Mpspreads!$A$1:$BM$55,MATCH(A12&B12,Mpspreads!A:A&Mpspreads!C:C,0),MATCH(Sheet1!C12,Mpspreads!$1:$1,0)),INDEX(Mpspreads!$A$1:$BM$55,MATCH(A12&B12,Mpspreads!B:B&Mpspreads!C:C,0),MATCH(Sheet1!C12,Mpspreads!$1:$1,0))))

This is array formula that should be applied with CTRL+SHIFT +ENTER

Put this formula in D2:

=INDEX(Mpspreads!$D$2:$BM$55,MATCH(1,((A2>=Mpspreads!$A$2:$A$55)*(A2<=Mpspreads!$B$2:$B$55)*(B2=Mpspreads!$C$2:$C$55)),0),MATCH(Sheet1!C2,Mpspreads!$D$1:$BM$1,0))

and copy it down. My concern is it may not work for you because you are on version 2007. If it doesn't work as is, try entering it as an array formula (with Ctrl+Shift+Enter). I'm on version Microsoft Office 365.