date specific, from multiple rows and columns

[dineshchaure26]

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.

[Limor_OP]

Hi

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

Please Login or Register  to view this content.

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

[dineshchaure26]

Hi Belinda:

Thank you for your prompt response.

I am sorry, I wasn't information. The formula works fine, however there shouldn't be any "no found" result. For example, if the formula is looking for 1/15/2020 date, it should fetch value from date range (start date/end date) of 1/14/2020 and 2/11/2020.

So any event date mentioned in sheet1, will have to be searched within the date range from MPspreads tab.

[Gregb11]

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.

[dineshchaure26]

Hi Greg:

The Formula worked in 2007. Thanks much.

Used it as an array formula.

[Gregb11]

Ahh great - glad it worked!