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))))))