INDEX(MATCH => Using IF Statement

NEW_2VBA

Board Regular
Joined
Dec 15, 2015
Messages
103
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")
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,958
Helper columns are your friend.
Put the first formula in X2 and then put the formula =IF(F2="Client1",X2, "not one client") in some other cell.

I know, there are huge monster formulas on this site often, but in practice, its much easier to create (and maintain) formulas that get the right result by using helper columns, than it is to decipher monsters.
 

NEW_2VBA

Board Regular
Joined
Dec 15, 2015
Messages
103
This sort of works; --> the formula in X2 has specific cell reference so I'm unsure how to make it relative since I'm indirectly using it in an IF statement.
When I copied & pasted down it populated results based on date in E2 only.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,443
Messages
5,624,802
Members
416,054
Latest member
Ariel2219

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top