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.