Contract rule changing depending on date using countifs and vlookup

[rfigueroa1976]

Hello all.

I'm trying to have a formula look at a list of contracts and come back with the contract rule depending on the date of an order. For example, I have a list of several contracts (listed on a separate sheet of the workbook) but one customer has two separate contracts for one customer - one (the older contract) with effective dates of of 01/01/19 - 04/30/2019 and another (the new contract) with effective dates of 05/01/19 - 04/30/2020. Each of these contracts has a specific rule (days to charge the customer). On the old contract, the rule is to bill 3 days, the new 5 days. The rules are for several customers (Several different names).

On the list of orders, there are different orders created that fall into these different contract rules. The order dated 04/29/2019 should come back with a rule bill 3 days. The order dated 05/03/2019 should come back with a rule bill 5 days.

The formula I thought would easily work is =IF(COUNTIFS(Contracts!G:G, "<="&Order!C2, Contracts!H:H, ">="&Order!C2,Contracts!B:B, "*"&A2&"*"), VLOOKUP("*"&A2&"*", Contracts!B:I, 3, FALSE), "No Rule")

You might ask the question "why the wild cards for the name look up?". The reason is the entry point is manual (by a person) and for ease of that, they are only entering the key word for the company name but the contract shows the full name... for example: Entry of Richard would bring back the rule for RICHARD NICKS AND CO.

Please help me figure this out. Need the correct rule to come back depending on the order date. Thanks. Workbook attached.

[AliGW]

Administrative Note:

We would very much like to help you with your query, however the thread title does not really convey what your request is about.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve.)

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)

[EchoPassenger]

One way - Enter this as an array formula in Cell D2 on your Orders sheet and copy down...

Please Login or Register  to view this content.

As this is creating an array, we can't use whole column references, so dependent on the number of rows in your Contract list, you will need to update the end row references from 3 to your last row.

Just to clarify the SEARCH function for you, I don't believe (I could be wrong) that you can use wildcards when it's just an = comparison.

[rfigueroa1976]

Changed. Hopefully that helps. Really struggled to figure out what to title it.

[rfigueroa1976]

One way - Enter this as an array formula in Cell D2 on your Orders sheet and copy down...

Please Login or Register  to view this content.

As this is creating an array, we can't use whole column references, so dependent on the number of rows in your Contract list, you will need to update the end row references from 3 to your last row.

Just to clarify the SEARCH function for you, I don't believe (I could be wrong) that you can use wildcards when it's just an = comparison.

This partially worked for me... Can you help with the issue I'm having... The problem is when A2 is blank. The formula populates whatever it wants right now (randomly selects one of the contract rules). Is there a way to have the formula say "No Rule" if A2 is blank or, maybe come back as "#N/A"?

INDEX(Contracts!D$2:D$3,MATCH(1,(IFERROR(SEARCH(A2,Contracts!B$2:B$3),0)*(C2>=Contracts!G$2:G$3)*(C2<=Contracts!H$2:H$3)),0))

[rfigueroa1976]

Any additional help on this?

This partially worked for me... Can you help with the issue I'm having... The problem is when A2 is blank. The formula populates whatever it wants right now (randomly selects one of the contract rules). Is there a way to have the formula say "No Rule" if A2 is blank or, maybe come back as "#N/A"?

INDEX(Contracts!D$2:D$3,MATCH(1,(IFERROR(SEARCH(,Contracts!B$2:B$3),0)*(C2>=Contracts!G$2:G$3)*(C2<=Contracts!H$2:H$3)),0))

[rfigueroa1976]

Can anyone else help on this?

[EchoPassenger]

Apologies for the delay in returning. You just need to add an IF function to deal with a blank value.

Please Login or Register  to view this content.

[rfigueroa1976]

Thanks for the reply. I will try that Monday.