I have two sheets in my excel workbook. I am trying to figure out a formula that will look at the symbol on sheet 1 (AA) and the date (3/6/2000) and use the information from sheet 2 plug in the info on sheet 1. The date in column A in sheet 1 needs to be the greater than or equal to the date on sheet 2 and has to be the closest date to the date on sheet 1. So even though 2/22/2000 and 2/15/2000 is less than 3/6/2000 the closest date that is less than 3/6/2000 would be 3/1/2000. In this example the input I'm looking for in sheet1 row2 for columns C,D,E,F would be 4,72,3,59
Sheet 1
A B C D E F
1 BuyDate Symbol CompRating GranularRating EarningsQualityRating GranularEarningRating
2 3/6/2000 AA
Sheet 2
A B C D E F
1 ValuationDate Symbol CompRating GranularRating EarningsQualityRating GranularEarningRating
2 2/15/2000 AA 5 83 4 67
3 2/22/2000 AA 4 63 3 55
3 3/1/2000 AA 4 72 3 59
4 3/8/2000 AA 5 98 5 89
5 3/15/2000 AA 2 34 2 21
My feeble attempt: My thought process was using an =if(and(statement with a vlookup or match but couldn't figure out how to get the date in column A in sheet 1 to say >= the date on sheet 2 column A as well as the closest date that is less than the date on sheet 1.
Bookmarks