PROBLEM: In Excel 2000, I have a table with (w/) a formula (described in the
Background section 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 tried editing the formula to include the new workbook but ended up
getting an error. So, for simplicity's sake, I modified my existing formula
to find the rates for those criteria which weren't found by WB1 (basically,
the bottom half of my current table).

However, aside from correctly displaying the rate for the first company it
finds, it's incorrectly displaying the last rate found in WB2 for all the
remaining companies. I can't seem to figure out why it's doing this.
Eventually I want to have 1 unified formula for the whole table, but for now
(while I'm troubleshooting this part of the formula), I want to fix this
formula to find info from WB2 only.

BACKGROUND: My original formula finds companies that match multiple
criteria on one of my worksheets and then inserts the pay rate for these
companies in a separate column on that worksheet based on a pay rate table
located, currently, in a different workbook (WB1). This array-entered
formula works fine and is as follows:

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

where from the WB1.xls file, col O contains the pay rates to be found and S
contains the Company names to be matched against based on values in my sheet.
From my worksheet, row 1 has headers in it. Col J is "Type", col K
"Program", col L "Model", col AD "Company", and col AJ is where I want the
rates to appear. In the formula, columns, J, K, & L are concatenated with
/s to match the values found in col K of the WB1.xls Coded sheet (Example of
col K: Networks/Res/Home; following the format $J2/$K2/$L2).

For the bottom half of my table where rates weren't found in WB1, I
simply changed the formula to look only in WB2. The modified array-entered
formula is:

=IF(L140="Level
1",100,INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,('[WB2.xls]Rates'!$C$2:$C$411=$AD140)*('[WB2.xls]Rates'!$D$2:$D$411=$K140)*('[WB2.xls]Rates'!$E$2:$E$411=$L140)),0))

where in WB2, col O still contains the pay rates, but the following
locations are different from the original formula: col C contains the
Company names to be matched against, col D "Program", and col E "Model".
Unlike WB1, no concatenation is needed because in WB2, the Program and Model
are in separate columns. As stated earlier, in my table, col AD is
"Company", col K "Program", & col L "Model". I don't need to match col J
"Type". Sorry if this is lengthy but I wanted to be as specific as possible.

"RS" wrote...
PROBLEM: In Excel 2000, I have a table with (w/) a formula (described in
the Background section 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 tried editing the formula to include the new workbook but ended
up getting an error. . . .

What error PRECISELY?

However, aside from correctly displaying the rate for the first company it
finds, it's incorrectly displaying the last rate found in WB2 for all the
remaining companies. . . .
....
For the bottom half of my table where rates weren't found in WB1, I simply
changed the formula to look only in WB2. The modified array-entered
formula is:

=IF(L140="Level 1",100,
INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)
*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140)),0))
....

This reformats as

=IF(
L140="Level 1",
100,
INDEX(
'[WB2.xls]Rates'!$O$2:$O$411,
MATCH(
1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)
*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140)
),
0
)
)

That is, the 0 is treated as 3rd argument to INDEX rather than 3rd argument
to MATCH. It should be

=IF(L140="Level 1",100,INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140),0)))


Reformatting formulas as if they were structured programming code is one of
the handier formula debugging techniques. It can make it easier to spot
errors like this.

Dear Harlan,

It wasn't so much an error as it was simply incorrectly displaying the last
rate in WB1. Your fix works great. How would I combine the 2 formulas such
that the formula would search both WB1 & WB2 w/ their respective criteria
together? The first formula for searching WB1 is:

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

Your corrected formula (modified for row 2) for searching WB2 is:

=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.

"Harlan Grove" wrote:

"RS" wrote...
PROBLEM: In Excel 2000, I have a table with (w/) a formula (described in
the Background section 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 tried editing the formula to include the new workbook but ended
up getting an error. . . .

What error PRECISELY?

However, aside from correctly displaying the rate for the first company it
finds, it's incorrectly displaying the last rate found in WB2 for all the
remaining companies. . . .
....
For the bottom half of my table where rates weren't found in WB1, I simply
changed the formula to look only in WB2. The modified array-entered
formula is:

=IF(L140="Level 1",100,
INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)
*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140)),0))
....

This reformats as

=IF(
L140="Level 1",
100,
INDEX(
'[WB2.xls]Rates'!$O$2:$O$411,
MATCH(
1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)
*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140)
),
0
)
)

That is, the 0 is treated as 3rd argument to INDEX rather than 3rd argument
to MATCH. It should be

=IF(L140="Level 1",100,INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140),0)))


Reformatting formulas as if they were structured programming code is one of
the handier formula debugging techniques. It can make it easier to spot
errors like this.

UPDATE TO PREVIOUS POST:
Using the same logic, how would I combine the 2 following equations into one?

Equation 1:

=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 2:

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

"RS" wrote:

Dear Harlan,

It wasn't so much an error as it was simply incorrectly displaying the last
rate in WB1. Your fix works great. How would I combine the 2 formulas such
that the formula would search both WB1 & WB2 w/ their respective criteria
together? The first formula for searching WB1 is:

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

Your corrected formula (modified for row 2) for searching WB2 is:

=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.

"Harlan Grove" wrote:

"RS" wrote...
PROBLEM: In Excel 2000, I have a table with (w/) a formula (described in
the Background section 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 tried editing the formula to include the new workbook but ended
up getting an error. . . .

What error PRECISELY?

However, aside from correctly displaying the rate for the first company it
finds, it's incorrectly displaying the last rate found in WB2 for all the
remaining companies. . . .
....
For the bottom half of my table where rates weren't found in WB1, I simply
changed the formula to look only in WB2. The modified array-entered
formula is:

=IF(L140="Level 1",100,
INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)
*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140)),0))
....

This reformats as

=IF(
L140="Level 1",
100,
INDEX(
'[WB2.xls]Rates'!$O$2:$O$411,
MATCH(
1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)
*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140)
),
0
)
)

That is, the 0 is treated as 3rd argument to INDEX rather than 3rd argument
to MATCH. It should be

=IF(L140="Level 1",100,INDEX('[WB2.xls]Rates'!$O$2:$O$411,MATCH(1,
('[WB2.xls]Rates'!$C$2:$C$411=$AD140)*('[WB2.xls]Rates'!$D$2:$D$411=$K140)
*('[WB2.xls]Rates'!$E$2:$E$411=$L140),0)))


Reformatting formulas as if they were structured programming code is one of
the handier formula debugging techniques. It can make it easier to spot
errors like this.