# INDEX(MATCH => Using IF Statement

#### NEW_2VBA

##### Board Regular
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))))

=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")

### 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
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
Thanks! I'll try that!

#### NEW_2VBA

##### Board Regular
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.

Replies
1
Views
149
Replies
11
Views
201
Replies
5
Views
209
Replies
4
Views
66
Replies
3
Views
209

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.

### Which adblocker are you using?

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

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