IF statement that also uses index match to verify if date falls with date range

Hi All,

With reference to the attached.

I have a table for projected revenue sales covering the period Jan-17 - Dec-25 (see table L17 : DO113). The revenues in this table are expressed in dollars. These revenues are a function of the information input into table B17 : J113.

The table B17 : J113 lists all the different revenue streams and contains fields (for user input) which assist in the quantification of those revenues. Column E in this table is effectively the price list (for the next 8 years....) that is applicable to all customers and products. It is key to know that the same product can be sold to two different customers for two different prices e.g row 22 shows Product A being sold to Customer A for $30. Row 31 shows Product A being sold to Customer D for $12.50.

For the purpose of this exercise, the prices in column E are fixed for each customer and not subject to increase or decrease at any time with the exception of prices for Customer A. You will see Customer A is typically being charged a higher rate than all other customers and as such, this customer demands regular price decreases over the course of a number of years.

So whilst the 'Expected Monthly Revenue' (column G of table B17 : J113 - which are also shown in every column of table L17 : DO113) should be fixed for all customers except customer A, customer A's 'Expected Monthly Revenues' should read "See Rate Reduction Table". The revenues for customer A in the table L17 : DO113 should be a function of the expected sales units (column F) multiplied by the applicable rate for that month, and the applicable rate for that month is shown in table B2 : L10.

The problem is, I don't know how to create a formula in cells L20: DO113 that gives a revenue value based on the following criteria:
- If customer in column D = "Customer A" then multiply expected sales units (column F) by the rate in place in the relevant month as per table B2 : L10. The relevant month being shown in cells L17 : BO17
- If not, the existing formula is these cells applies

No doubt any solution will use an IF statement (IF customer = "Customer A" ...) which incorporates an index match function allowing for the comparison (greater than, equal to or less than) of current month as per table L17 : BO113, to the month in the table B2 : L10, thereby generating a singular rate to be applied to sales unit activity.

Sorry, this is something I should have defined to make things easier for everyone.

Cell L20 is currently showing the correct value because column L represents the month of January 2017 and row 20 shows sales of Product C to Customer A. As per the rate reduction table (B2 : L10), the rate for sales of Product C to Customer A is $40 for the months Oct-16 - Sep-17. From Oct-17, this rate for this product decreases to $23.50.

Hence in columns L - T (Oct-16 - Sep-16) the sales revenue for row 20 should show as $60,040 (units of 1,501 x $40). From columns U - AF the sales revenue for row 20 should show as $38,275.50 (1,501 units x $25.50) and so on.

Try in L20

Please Login or Register  to view this content.

Hi John,

I've tried this formula but it doesn't mean to work. I'm still getting the same values across columns L - BO i.e. revenues aren't reducing in line with rate change.

I don't know if this impacts on your formula, but in answer to Richard Buttreys question, cells C3:L3 show the effective date of the rate change. By way of reference to the spreadsheet, cells C3:C10 show the current rates being charged (being charged from Oct-16). These rates do not change until Oct-17 (D3:D10) which infers (and this is actually the case) that the rates up until Oct-17 i.e. up to 30-Sep-2017 will still be the rates shown in cells C3:C10. Hopefully that makes sense.

Try

=IF($D20="Customer A",$F20*INDEX($C$4:$L$9,MATCH($C20,$B$4:$B$10,0),MATCH(L$17,$C$3:$L$3,1)),IF($J20="No",0,IF(AND(O$17>=$H20,O$17<$I20),$G20,0)))


Typo by me!!!

Thanks John, perfect solution, as always.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.