In the attached excel file, I have a index match formula in cell B2 to return an approximate value for the input date in cell B1. It returns the value I would expect from the data set, ignoring the blank cells. I expanded the data in cells E6-I18 and have another formula in cell F2 that returns the appropriate value if the data set has no blank cells. I have similar data in columns L thru P, but I made cells N10 & N11 blank for dates 1/1/2002 & 1/1/2003. I want a formula that will return the value of 13 from date 1/1/2001 with an input date of 2/15/2003 and GP = 3 and Band = 2 and not return a value of zero from the blank cell for date 1/1/2003. I tried combining the formulas from cells B2 & F2 to produce a new formula in cell M2 that returns the correct value, but I had to include if/then statements to reference the correct columns. The entire data set that I will be working with is about 30 columns wide, so I do not want to write a formula with that many if/then statements included in the index match formula. Any ideas on how to simplify the formula in cell M2 to be more dynamic for an expanded set of data?
Bookmarks