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