Hi,
I have a spreadsheet with multiple sheets that each contain a table of data which contains a series of dates and other criteria with which I want to do a comparison. The basic structure of the columns is as follows: date, criteria1,criteria2, criteria3,desired value column.
I am trying to create a formula that will pull the value from each table that corresponds to the closest date less than or equal to the date specified that meets the desired value in the columns for criteria1, criteria2, and criteria3. I though about using the SUMPRODUCT formula since it will make all values that don't meet one or more of the criteria equal to zero with the structure =SUMPRODUCT(('Sheet Name'!A2:A20<=date desired)*('Sheet Name'!B2:B20=criteria1 value)*('Sheet Name'!C2:C20=criteria2 value)*('Sheet Name'!D2:D20=criteria3 value)*'Sheet Name'!E2:E20)
My concerns with this formula are:
1) What happens if there are multiple date values that meet the criteria values provided for criteria1, criteria2, criteria3? How do I tell the formula to only give me the most recent value for this subset?
2) The above example formula is not dynamic and I attempted to modify this by using the INDIRECT formula since I have the sheet names listed within cells on the sheet I am trying to use the above formula but excel kept throwing a "#REF!" error. It would also not work when I tried to use the INDIRECT formula to reference a single cell rather than a range. Does this have anything to do with whether or not the sheet name has a space in it?
I also thought about using a combination of the INDEX and MATCH formulas as follows {=INDEX('Sheet Name'!E2:E20,MATCH(1,('Sheet Name'!A2:A20<=date desired)*('Sheet Name'!B2:B20=criteria1 value)*('Sheet Name'!C2:C20=criteria2 value)*('Sheet Name'!D2:D20=criteria3 value))} again replacing the Sheet Name callout with a dynamic reference to to the cell containing the sheet name using the INDIRECT formula.