Conditional Vlookup- dates identifier and codes

[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 code + 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.

Thanks in advance for the help.

 

[Marcelo Branco]

Hi and welcome to Mr Excel Forum,

Assuming your data sample in A1:C9 (headers in row 1) and that E2 houses GB0007980591/Q1-2004, maybe this array formula

=INDEX($B$2:$B$9,MATCH(1,IF($A$2:$A$9=$E$2,IF($C$2:$C$9=MAX(IF($A$2:$A$9=$E$2,$C$2:$C$9)),1)),0))

confirmed with Ctrl+Shift+Enter
(hold down both Ctrl and Shift keys and hit Enter)

M.

 

[tusharm]

Suppose your data are in B12:D19. Suppose your target ISIN + quarter is in F12 and the end of the quarter date is in H12. Then, in G12 enter the array formula =INDEX($C$12:$C$19,MAX((ROW($C$12:$C$19)-ROW($C$12)+1)*($B$12:$B$19=F12)*($D$12:$D$19<=H12)))

To enter an array formula complete data entry with the CTRL+SHIFT+ENTER combination rather than just the TAB or ENTER key. If done correctly, *Excel* will show the formula enclosed in curly brackets { and }

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 code + 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.

Thanks in advance for the help.

 

[micante]

It works perfectly.
A big thanks to you, Marcelo Branco and Tusharm. After all it did not even need Vlookup. I was completely off track on how to solve this problem. I really appreciated your help.
Andre