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.