Multiple Conditions with Date Range

[caippers]

I'm trying to create a formula that will return a result based off of a match in 3 columns in a row and if that doesn't match then I want the third column result to default to a variable of $$$$$. I have attached an example.

In the example attached I want to return a value from column K "Result/TableID from the table on the workbook listed "StaticTable" into cell 3F on the variable table workbook based off of a match on column A,B,C,D, and E being a date (from Variable workbook) I want to fall in between column E and F in the static table.

my first problem:

I want to match on the first three columns from the variable table first (product, plan, state) the problem with this is the variable table will list all of the states and the static table lists a few states and then defaults to a $$$$$ variable for all others. an example of this would be FRE, +, IA. If you view this selection on the StaticTable it will not be listed because I would want it to match on FRE,+,$$$$$ on the static table. If I had FRE,+,CT it does match on the first three columns and I would want that returned.

Second issue which is not as important because I can take out the date range if I have to. I want the Bonus Rate Date from the variable table to bring back a result from the static table that is between or = to the date between column E and F (static table)

In short I want to match 3 columns to 3 and if the 3 columns don't match match the first 2 and default the value for the third column to $$$$$ and match the date to be between Bonus Rate date and Bonus Rate End Date.

Any help is truly appreciated.

[NBVC]

Try:

Please Login or Register  to view this content.

[caippers]

I tried this and it did return a result for Row 3 and Row 9 in the variableTable worksheet. This appears to be two items that contain the 3 column match and it doesn't appear to be matching on the two columns.

[NBVC]

I am not sure what you are saying...

Column 3 and 9 have matches to FRE,X,IN and FRE,+,CT, respectively and are within date ranges specified.

The other rows did not match in all categories.

[caippers]

I agree. I'm having a hard time explaining this one. What I'm trying to do for ones that don't have a match is use a value of $$$$$ in place of that third column that doesn't match.

So in the example you gave me row 2 of the variable table would have returned a result based off of FRE, X, $$$$$

I have to use the $$$$$ because there is no exact match for the SC value. In this case I would want to return the value from K33 in the static table:

FRE, X , $$$$$, 06-01-2010 which falls between 09/14/2009 and 09/06/2010

The same would be the case for row 5,6, and 7 on the variable table. In this case I would want it to match on

FRE,3,$$$$$
FRE,5,$$$$$
FRE,7,$$$$$

I am forced to pull back a state in my variable table but the static table does not contain a state for every scenario. When the state matches I want to use that value. If there is no match for the state I want to use the first two columns plus the value of $$$$$ for the third column which should return a value.

I hope I'm explaining this ok. Let me know if you have any question feel free to IM me or PM so I can better explain.

[NBVC]

Do you mean?

Please Login or Register  to view this content.

[caippers]

Absolutely OUTSTANDING. Works for me perfectly. If you don't mind or have a second could explain a little bit about the logic of this formula?

It works exactly how I need it to. I will close post.

[NBVC]

Each of the arguments in this combination:

StaticTable!$A$2:$A$39=B3)*(StaticTable!$B$2:$B$39=C3)*(StaticTable!$C$2:$C$39=IF(ISNUMBER(MATCH(D3,StaticTable!$C$2:$C$39,0)),D3,REPT("$",5)))*(StaticTable!$E$2:$E$39<=E3)*(StaticTable!$F$2:$F$39>=E3)

creates an array of TRUEs and FALSEs based on if the individual conditions at each row in the ranges are true or not.

Note: this particular argument: (StaticTable!$C$2:$C$39=IF(ISNUMBER(MATCH(D3,StaticTable!$C$2:$C$39,0)),D3,REPT("$",5)))

is based on your last clarification and says to find if there is a Match to D3 in range StaticTable!$C$2:$C$39. If so, it will return TRUE and will carry on equating D3 to StaticTable!$C$2:$C$39 and returning TRUE at that point, if not TRUE, then is will substitute a $$$$$ (REPT("$",5) means repeat "$" 5 times) and it will use that as the condition to match for StaticTable!$C$2:$C$39 range...along with the other conditions.

After each array is built they are multiplied together and multiplying TRUEs and FALSEs is like multiplying 1s and 0s, respectively, so the outcomes is one array of 1s and 0s.... there should only be one 1 in the array if you have one row that matches all conditions... and this 1 is at the position where all the elements match your separate conditions.

Then INDEX() holding those arguments above is used to create and hold that resultant array.

The MATCH(1,INDEX(),0) that surrounds it, looks for that 1 in the resultant array and brings back the position... which is then INDEXed against the StaticTable!$K$2:$K$39 range.

The IFERROR() is a new 2007 feature and is just an error trap.. if the INDEX() function returns an error (because no matches were found), it will replace with whatever you indicate in the 2nd argument of this function... I chose to return a null.

Hope that helps.

[caippers]

Your help is very much appreciated as well as the great explanation.