Referencing multiple values to determine an equation

Joecam

New member
Joined
May 22, 2014
Messages
34
Reaction score
0
Points
0
Excel Version(s)
365
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%

Limit (Expressed in $)
Ded.Up to 59,99960,000 to 99,999100,000 to 200,000200,001 & Over
5000.980.980.970.97
750-0.950.940.94
1000--0.860.86
1500--0.820.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
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!
 
Last edited:

NBVC

Super Moderator
Staff member
Joined
May 20, 2011
Messages
1,518
Reaction score
0
Points
0
Location
Mississauga, Canada
Excel Version(s)
Excel 2016
Assuming your table is in Shee1, row headers starting at A3 (i.e. 500 is in A3, 750 is in A4, etc) and in B2 across you enter only the lower bounds of your ranges (i.e. 0, 60000, 100000, 200001)

Then assuming, your input information is in another sheet, try this formula:

=D24*INDEX(Sheet1!$B$3:$E$14,MATCH(E24,Sheet1!$A$3:$A$14,0),MATCH(D2,Sheet1!$B$2:$E$2))
 
Top