# Referencing multiple values to determine an equation

#### Joecam

##### Member
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,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
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
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))