Re: INDEX/MATCH skipping results
- From: RS <RS@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Sat, 18 Aug 2007 13:38:03 -0700
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" <RS@xxxxxxxxxxxxxxxxxxxxxxxxx> 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.
- References:
- Re: INDEX/MATCH skipping results
- From: Harlan Grove
- Re: INDEX/MATCH skipping results
- From: RS
- Re: INDEX/MATCH skipping results
- Prev by Date: Re: Using Named Formulas
- Next by Date: Re: Random Number Generation & VLOOKUP
- Previous by thread: Re: INDEX/MATCH skipping results
- Next by thread: Re: Pivot Table COUNTA SUM of Unhidden cells only
- Index(es):
Relevant Pages
|
