Combining If and Lookup for Complicated Data

[than_k22]

Hi

I have to match year-ended of each firm with market value (MV). In my criteria, I am looking for value of MV 4 months after the firm's year-ended.

For example: If firm A has its year-ened 30/06/07, I am looking for MV at the date 31/10/07 (another issue is sometimes the date for MV can be 29/10/07, not 31/10/07; however it can be varied +- 7 days of the firm's year-ended date).

Please can you help me how to match these data. I also attach the sample of my data.

Thank you very much.

[MarvinP]

Hi than_k22,
There is not data attached. Click on "Go Advanced" and then the paperclip icon above the message area to attach a file. You can press the Green Edit button to get back to your message to attach the file.

[than_k22]

Thanks... the sample is already attached.

[DonkeyOte]

Based on the sample - and assuming "dscodes" in results table are not always to be listed in identical fashion to the source table listing

L3:
=IF(ISNUMBER(C3),LOOKUP(DATE(YEAR(C3),MONTH(C3)+5,0),$C$15:$AZ$15,INDEX($C$16:$AZ$23,MATCH($B3,$B$16:$B$23,0),0)),"NA")
applied to matrix L3:N10