I have four tables that I'm trying to reference data from and compare it to manually input data in another area of my spreadsheet to determine what factor should be used in a specific line equation.
The tables all have the same ranges and are setup like this; where A% equals 1%, 2%, 3% or 5%, referencing one of the 4 tables.
A%
The values in the table change, however, the first column doesn't.
I'm trying to avoid writing a very lengthy IF statement if possible. Basically I need a formula that says:
IF E25 equals A% reference table X AND match E24 to column 1 AND based off of value D2, determine which column's factor should be referenced to determine the final equation that should be run.
If that is confusing, below is a copy of the IF statement I started to write which might give a better idea as to what I'm trying to do:
IF(AND(E25=1%,D2<60000,E24=500),D24*.98,etc.
Any help is appreciated!
The tables all have the same ranges and are setup like this; where A% equals 1%, 2%, 3% or 5%, referencing one of the 4 tables.
A%
Limit (Expressed in $) | ||||
Ded. | Up to 59,999 | 60,000 to 99,999 | 100,000 to 200,000 | 200,001 & Over |
500 | 0.98 | 0.98 | 0.97 | 0.97 |
750 | - | 0.95 | 0.94 | 0.94 |
1000 | - | - | 0.86 | 0.86 |
1500 | - | - | 0.82 | 0.82 |
2500 | - | - | - | 0.74 |
5000 | - | - | - | 0.65 |
7500 | - | - | - | 0.61 |
10000 | - | - | - | 0.59 |
12500 | - | - | - | 0.56 |
15000 | - | - | - | 0.53 |
25000 | - | - | - | 0.49 |
50000 | - | - | - | 0.41 |
I'm trying to avoid writing a very lengthy IF statement if possible. Basically I need a formula that says:
IF E25 equals A% reference table X AND match E24 to column 1 AND based off of value D2, determine which column's factor should be referenced to determine the final equation that should be run.
If that is confusing, below is a copy of the IF statement I started to write which might give a better idea as to what I'm trying to do:
IF(AND(E25=1%,D2<60000,E24=500),D24*.98,etc.
Any help is appreciated!
Last edited: