Hello!
The formula below populates the Billing Month from another Lookup table based on the Date Range.
It works, however I'm wondering, if there's a simpler way to rewrite this formula to include an IF statement. I need to reference a different Range for the INDEX, based on the the client Names in column F.
Current formula:
=INDEX('BillingSheet'!$J$3:$J$15,SUMPRODUCT((E2>='BillingSheet'!$K$3:$K$15)*(E2<='BillingSheet'!$L$3:$L$15)*MATCH(ROW('BillingSheet'!$J$3:$J$15),ROW('BillingSheet'!$J$3:$J$15))))
Need to add:
=IF(F2="CLIENT1",INDEX('BillingSheet'!$J$3:$J$15,SUMPRODUCT((E2>='BillingSheet'!$K$3:$K$15)*(E2<='BillingSheet'!$L$3:$L$15)*MATCH(ROW('BillingSheet'!$J$3:$J$15),ROW('BillingSheet'!$J$3:$J$15)))),"I think here I would copy the formula using different Range IF F2 = CLIENT2 & CLIENT3")
The formula below populates the Billing Month from another Lookup table based on the Date Range.
It works, however I'm wondering, if there's a simpler way to rewrite this formula to include an IF statement. I need to reference a different Range for the INDEX, based on the the client Names in column F.
Current formula:
=INDEX('BillingSheet'!$J$3:$J$15,SUMPRODUCT((E2>='BillingSheet'!$K$3:$K$15)*(E2<='BillingSheet'!$L$3:$L$15)*MATCH(ROW('BillingSheet'!$J$3:$J$15),ROW('BillingSheet'!$J$3:$J$15))))
Need to add:
=IF(F2="CLIENT1",INDEX('BillingSheet'!$J$3:$J$15,SUMPRODUCT((E2>='BillingSheet'!$K$3:$K$15)*(E2<='BillingSheet'!$L$3:$L$15)*MATCH(ROW('BillingSheet'!$J$3:$J$15),ROW('BillingSheet'!$J$3:$J$15)))),"I think here I would copy the formula using different Range IF F2 = CLIENT2 & CLIENT3")