Conditional Vlookup- Identify firm and closest observation to a specific date

[micante]

Hey,

I am really struggling to create a conditional VLOOK function. The VLOOK should identify not only the firm but also the rating that was attributed to the firm closest to a specific date ( end of quarter).
E.G. I have a firm that has several rating in January , February, March but I only want the vlookup function to give me the firm rating that is closest to the end of Quarter 1 (31-03-2004).

Do you have any suggestions?

ISIN+ Quarter: Rating Day
US92553P2011/Q1-2004 1 01-01-2004
US92553P2011/Q1-2004 9 02-01-2004
US92553P2011/Q1-2004 13 03-01-2004
US92553P2011/Q1-2004 16 04-01-2004
GB0007980591/Q1-2004 5 08-01-2004
BRPETRACNPR6/Q1-2004 27 08-01-2004
GB0007980591/Q1-2004 3 09-01-2004
BRPETRACNPR6/Q1-2004 8 09-01-2004

So , Basically if we are talking about "GB0007980591/Q1-2004" I would like the Vlookup function to return the rating: 3.

I also attached the excel so that you can have a better idea of what I am talking about.

Thanks in advance for the help.
Conditional Vlook- Help.xlsxConditional Vlook- Help.xlsxAndre

[micante]

Hey,
Sorry for the table misformatting ,

Here it is :

ISIN+ Quarter: Rating Day
US92553P2011/Q1-2004 1 01-01-2004
US92553P2011/Q1-2004 9 02-01-2004
US92553P2011/Q1-2004 13 03-01-2004
US92553P2011/Q1-2004 16 04-01-2004
GB0007980591/Q1-2004 5 08-01-2004
BRPETRACNPR6/Q1-2004 27 08-01-2004
GB0007980591/Q1-2004 3 09-01-2004
BRPETRACNPR6/Q1-2004 8 09-01-2004

And the two excel files that I attached in the previous thread are the same.

Andre