I have a table that is a rent schedule. This table has columns for 'Start Date', 'Base Rent', 'Building Cards', 'Parking Permits', 'Service Agreements', and 'OPEX rates'. The start dates are generally the first of a month, but they are not every month in the time period, only those months where changes occur in any of the columns to the right of 'Start Date'. This schedule is referenced by another portion of the workbook that includes every month during the time period. I'm using a Match function to determine the last date that was prior to or equal to the date in question to determine what monthly charges apply. Because I'm combining the charges from 'Building Cards' and 'Parking Permits', I've had to repeat the basic INDEX & MATCH formula to return the related value from both columns, and then sum the two values together. Alternatively, I could add another column that combines these two values together and just return that value, but when possible I try to make things hard on myself in case I come across a situation where I can't add a helper column and it gives me an example case to learn from.
Here is the formula that is working:
In the formula above, $B44 contains the date we are testing for. I'm first looking to see if the date is as of the end of the month. If not, we wouldn't want to calculate monthly charges, so we return a 0. 'AR_RentSched' is the name of the table.
I'd like to not have to repeat the formula just to combine the results from two columns where there is a match on the row. I'm guessing this might require an array formula, but I haven't been able to find one and my attempts to create one have not gone well. Is it possible to do what I want without having to add a helper column or repeat the INDEX and MATCH formula?
Here is the formula that is working:
Code:
=IF($B44-EOMONTH($B44,0)=0,INDEX(AR_RentSched[Building Cards],MATCH($A44,AR_RentSched[Start Date],1)),0)
+IF($B44-EOMONTH($B44,0)=0,INDEX(AR_RentSched[Parking Permits],MATCH($A44,AR_RentSched[Start Date],1)),0)
In the formula above, $B44 contains the date we are testing for. I'm first looking to see if the date is as of the end of the month. If not, we wouldn't want to calculate monthly charges, so we return a 0. 'AR_RentSched' is the name of the table.
I'd like to not have to repeat the formula just to combine the results from two columns where there is a match on the row. I'm guessing this might require an array formula, but I haven't been able to find one and my attempts to create one have not gone well. Is it possible to do what I want without having to add a helper column or repeat the INDEX and MATCH formula?