Combining 2 INDEX/MATCH eq'ns; also 2 Sumproduct eq'ns
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))) |
All times are GMT +1. The time now is 08:46 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com