Complex 2 table formula
 

Here are my two tables:

Table 1

Column 1 Col 2 Col 3 Col 4 Col 5
Col 6 Col 7
Row 1 Slope Bands (%) R1 RS RE9 RE11
RE15 RE20
Row 2 0 €“ 14 0.5 0.45 0.4 0.4 0.35 0.35
Row 3 15 €“ 29 0.45 0.4 0.35 0.35
0.3 0.3
Row 4 30 €“ 44 0.4 0.35 0.3 0.3
0.25 0.25
Row 5 45 €“ 59 0.35 0.3 0.25 0.25 0.2 0.2
Row 6 60 €“ 99 0.3 0.25 0.2 0.2 0.15
0.15
Row 7 100 + 0 0 0 0 0 0

Table 2
Col 1 Col 2 Col 3
Row 1 ZONE Area Slope
Row 2 R1-1 31.71 <15%
Row 3 R1-1 32.72 15-30%
Row 4 R1-1 63.70 30-45%
Row 5 R1-1 17.29 45-60%
Row 6 RE9-1 6474.83 45-60%
Row 7 RE9-1 19602.34 15-30%
Row 8 RE9-1 4438.14 <15%

What I need to be able to do is to create a formula that multiplies the
value in table 1 in columns 2-7 that corresponds to the slope and the zone
with the lot area in table 2. For instance, I need to find a formula that
will choose to multiply the value in table 1 Row 5, Col 4 with table 2 row,
6, column 3. I want it to know which value to pull from table 1 to multiply
with in table 2 so the zone and slope categories match up....Is this
possible?

What I have done as a workaround is to sort the data by the zone and then
apply formulas that I developed for each zone. i.e.
=IF(slope="<15%",
area*$Q$8,IF(slope="15-30%",Area*$Q$9,IF(slope="30-45%",Area*$Q$11,IF(slope="45-60%",Area*$Q$12,IF(slope="60-100%",Area*$Q$13,IF(slope="100%",Area*$Q$14))))))

Complex 2 table formula
 

I assume that your data is look like this€¦

Table1 in Sheet1 from A1:G7
Table2 in sheet 2 from A1:C8

In TABLE 1 (Sheet1)
Paste this formula in H2 cell
=IF(ISERROR(VALUE(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE( A2,"€“","-"),"+","")),FIND("-",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"€“","-"),"+","")))-1))),VALUE(SUBSTITUTE(SUBSTITUTE(A2,"€“","-"),"+","")),VALUE(LEFT(TRIM(SUBSTITUTE(SUBSTITUTE( A2,"€“","-"),"+","")),FIND("-",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"€“","-"),"+","")))-1)))

Copy the H2 cell and paste it to the remaining cells of H Column depends on
the A Column Data.

Paste this formula in I2 cell
=IF(ISERROR(VALUE(RIGHT(TRIM(SUBSTITUTE(SUBSTITUTE (A2,"€“","-"),"+","")),FIND("-",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"€“","-"),"+","")))-1))),10000000,VALUE(RIGHT(TRIM(SUBSTITUTE(SUBSTITU TE(A2,"€“","-"),"+","")),FIND("-",TRIM(SUBSTITUTE(SUBSTITUTE(A2,"€“","-"),"+","")))-1)))

Copy the I2 cell and paste it to the remaining cells of I Column depends on
the A Column Data.


In TABLE 2 (Sheet2)
Paste this formula in D2 cell
=IF(ISERROR(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(C2,"<" ,""),"%",""),FIND("-",SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%",""))-1))),0,VALUE(TRIM(LEFT(SUBSTITUTE(SUBSTITUTE(C2,"< ",""),"%",""),FIND("-",SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%",""))-1))))

Copy the D2 cell and paste it to the remaining cells of D Column depends on
the C Column Data.

Paste this formula in E2 cell
=IF(ISERROR(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C2,"< ",""),"%",""),FIND("-",SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%",""))-1))),VALUE(SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%","" )),VALUE(TRIM(RIGHT(SUBSTITUTE(SUBSTITUTE(C2,"<"," "),"%",""),FIND("-",SUBSTITUTE(SUBSTITUTE(C2,"<",""),"%",""))-1))))

Copy the E2 cell and paste it to the remaining cells of E Column depends on
the C Column Data.

In F2 cell Paste the below formula (This will bring you the result you have
expected)

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A2,FIND("-",$A2)-1),Sheet1!$B$1:$G$1)))*(Sheet1!$H$2:$H$7=Sheet2!$ D2)*(Sheet1!$I$2:$I$7<=Sheet2!$E2),(Sheet2!$B2*She et1!$B$2:$G$7))

Copy the F2 cell and paste it to the remaining cells of F Column depends on
the Table2 Data (Sheet2).

In the above formula change the cell reference $H$2:$H$7, $I$2:$I$7 &
$B$2:$G$7 to your desired range like this depends on your data on that column
$H$2:$H$100, $I$2:$I$100 & $B$2:$G$100 like the below:-

=SUMPRODUCT((ISNUMBER(SEARCH(LEFT($A2,FIND("-",$A2)-1),Sheet1!$B$1:$G$1)))*(Sheet1!$H$2:$H$100=Sheet2 !$D2)*(Sheet1!$I$2:$I$100<=Sheet2!$E2),(Sheet2!$B2 *Sheet1!$B$2:$G$100))

Apply it for the remaining cells of F Column.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"JD" wrote:

Here are my two tables:

Table 1

Column 1 Col 2 Col 3 Col 4 Col 5
Col 6 Col 7
Row 1 Slope Bands (%) R1 RS RE9 RE11
RE15 RE20
Row 2 0 €“ 14 0.5 0.45 0.4 0.4 0.35 0.35
Row 3 15 €“ 29 0.45 0.4 0.35 0.35
0.3 0.3
Row 4 30 €“ 44 0.4 0.35 0.3 0.3
0.25 0.25
Row 5 45 €“ 59 0.35 0.3 0.25 0.25 0.2 0.2
Row 6 60 €“ 99 0.3 0.25 0.2 0.2 0.15
0.15
Row 7 100 + 0 0 0 0 0 0

Table 2
Col 1 Col 2 Col 3
Row 1 ZONE Area Slope
Row 2 R1-1 31.71 <15%
Row 3 R1-1 32.72 15-30%
Row 4 R1-1 63.70 30-45%
Row 5 R1-1 17.29 45-60%
Row 6 RE9-1 6474.83 45-60%
Row 7 RE9-1 19602.34 15-30%
Row 8 RE9-1 4438.14 <15%

What I need to be able to do is to create a formula that multiplies the
value in table 1 in columns 2-7 that corresponds to the slope and the zone
with the lot area in table 2. For instance, I need to find a formula that
will choose to multiply the value in table 1 Row 5, Col 4 with table 2 row,
6, column 3. I want it to know which value to pull from table 1 to multiply
with in table 2 so the zone and slope categories match up....Is this
possible?

What I have done as a workaround is to sort the data by the zone and then
apply formulas that I developed for each zone. i.e.
=IF(slope="<15%",
area*$Q$8,IF(slope="15-30%",Area*$Q$9,IF(slope="30-45%",Area*$Q$11,IF(slope="45-60%",Area*$Q$12,IF(slope="60-100%",Area*$Q$13,IF(slope="100%",Area*$Q$14))))))

Complex 2 table formula
 

Excel 2007
Scariest (and only) formula used:
=INDIRECT(Zone) INDIRECT(Slope)
http://www.mediafire.com/file/gdj2yo...12_23_09a.xlsx

Complex 2 table formula
 

The difference between Mr. Herbert Seidenberg solution and the solution
provided by me is that, Mr. Herbert Seidenberg properly restructured the data
but I have retrieved the desired result using the formula without changing
the OP's Data Structure.

--------------------
(Ms-Exl-Learner)
--------------------


"Herbert Seidenberg" wrote:

Excel 2007
Scariest (and only) formula used:
=INDIRECT(Zone) INDIRECT(Slope)
http://www.mediafire.com/file/gdj2yo...12_23_09a.xlsx
.

Complex 2 table formula
 

Agreed

Complex 2 table formula
 

I tried both methods and neither have work successfully yet. However, the one
below seems the simplest. Can you walk me through how you created the names
and lists and how the indirect feature works? I think my biggest trouble is
figuring out what to do with both the "Slope" names...and the relationship
between the two indirect's.

Thanks so much for your help.


"Herbert Seidenberg" wrote:

Excel 2007
Scariest (and only) formula used:
=INDIRECT(Zone) INDIRECT(Slope)
http://www.mediafire.com/file/gdj2yo...12_23_09a.xlsx
.