Hi there: I'm finding some good info here, but not quite what I need just yet. I'm attempting to modify a current VLOOKUP formula (that works), but putting the information into one table for easier maintenance. By placing this in a table I've added a column for the Comp plan.
The current formula is using a range of values to identify if the comp falls within that range, and then the formula provides the Comm %. See example below. However, adding the Comp plan ID, I need something to perform that additional criteria. Don't worry about the UL column (at least I don't think we need to).
Comp Plan Sales UL Comm % SR1 0.00 24,999.99 2.00% SR1 25,000.00 49,999.99 3.50% SR1 50,000.00 + 4.00% SR2 0.00 74,999.99 2.00% SR2 75,000.00 124,999.99 2.75% SR2 125,000.00 + 3.50% SR4 0.00 99,999.99 1.00% SR4 100,000.00 199,999.99 2.25% SR4 200,000.00 + 3.00%
So, the formula should take the table above find the Comp plan and the associated Sales amount to compare it with the values that are selected in the main table. For example, SR1 with $85K sales would result in 4%. SR2 with 100K sales would result in 2.75%.
Please let me know the best way that this is possible. Thanks again for all your help! = )
Bookmarks