In Excel 2000, I have a table with (w/) a formula (Equation 1 below) that
finds & inserts pay rates from another workbook (let's call it WB1) based on
multiple criteria. I now have a 2nd workbook (call it WB2) w/ additional
rates that I want my table to also check. I've created a formula (Equation
2) to find the rates for those criteria which weren't found by WB1
(basically, the bottom half of my current table starting at row 140). How
can I combine the 2 formulas (from row 2 on down) such that the formula would
search both WB1 & WB2 w/ their respective criteria together?
Equation 1 (searches WB1 only; currently found in rows 2 - 139)
=IF(L2="Level
1",100,INDEX('[WB1.xls]Coded'!O$3:O$340,MATCH(1,('[WB1.xls]Coded'!$S$3:$S$340=$AD2)*('[WB1.xls]Coded'!$K$3:$K$340=CONCATENATE($J2,"/",$K2,"/",$L2)),0)))
Equation 2 (searches WB2 only; originally found from row 140 on; modified in
this post for row 2):
=IF(L2="Level 1",100,INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD2)*('[WB2.xls]Rates'!$D$2:$D$411=$K2)
*('[WB2.xls]Rates'!$E$2:$E$411=$L2),0)))
I've tried a few variations to combine the 2, but I'm not getting it right.
That's why Equation 1 was found in rows 2 €“ 139 and Eqn 2 was found in 140 &
beyond. If you want a description of the various parts of the formula see my
previous post:
http://www.microsoft.com/office/comm...c-f54b507d0440
Using the same logic, how would I combine the 2 following equations into one?
Equation 3:
=IF(L2="Skill Level
1","-",SUMPRODUCT(('[WB1.xls]Coded'!$S$3:$S$340=$AD2)*('[WB1.xls]Coded'!$K$3:$K$340=CONCATENATE($J2,"/",$K2,"/",$L2))))
Equation 4:
=IF(L2="Skill Level
1","-",SUMPRODUCT(('[WB2.xls]Rates'!$C$2:$C$411=$AD2)*('[WB2.xls]Rates'!$D$2:$D$411=$K2)*('[WB2.xls]Rates'!$E$2:$E$411=$L2)))
I tried the following formula but keep getting an #N/A error even though the
value of 1 is correctly displayed w/ Equation 1 only:
=IF(L2="Skill Level
1","-",SUMPRODUCT(('[WB1.xls]Coded'!$S$3:$S$340=$AD2)*('[WB1.xls]Coded'!$K$3:$K$340=CONCATENATE($J2,"/",$K2,"/",$L2))*('[WB2.xls]Rates'!$C$2:$C$411=$AD2)*('[WB2.xls]Rates'!$D$2:$D$411=$K2)*('[WB2.xls]Rates'!$E$2:$E$411=$L2)))