Trying to use INDEX and MATCH to insert a value w/ multiple cr
Dear Peo,
Thanks for the tip!
"Peo Sjoblom" wrote:
You can remove one "BBB"
=IF(K1="BBB",K1,INDEX(etc
--
Regards,
Peo Sjoblom
"RS" wrote in message
...
Dear Bob,
Thanks for your reply. I simplified your formula by making the first part
="BBB". The modified array-entered formula is:
=IF(K1="BBB","BBB",INDEX(Codes!$O$25:$O$33,MATCH(1 ,(Codes!$L$25:$L$33=Import!J1)*(Codes!$M$25:$M$33= Import!K1)*(Codes!$N$25:$N$33=Import!L1),0)))
The formula works beautifully now. Thanks so much for your help. I
noticed
in your formula that there was a space and a carriage return preceding the
2nd MATCH statement. Was this intentional or did this happen during the
course of your reply being posted? I tried inserting both a space only
and a
space & carriage return in my formula and both times, the formula still
worked. I didn't realize you could do this. It definitely makes it
easier
to quickly identify the different halves of the formula.
"Bob Phillips" wrote:
=IF(K1="BBB",INDEX(Codes!$O$25:$O$33,MATCH(1,(Code s!$L$25:$L$33=Import!J1)*(Codes!$N$25:$N$33=Import !L1),0)),
INDEX(Codes!$O$25:$O$33,MATCH(1,(Codes!$L$25:$L$33 =Import!J1)*(Codes!$M$25:$M$33=Import!K1)*(Codes!$ N$25:$N$33=Import!L1),0)))
--
---
HTH
Bob
(there's no email, no snail mail, but somewhere should be gmail in my
addy)
"RS" wrote in message
...
In Excel 2000, I'm trying to create a formula whereby it finds
companies
that
match multiple criteria (Category, Case Type, & Model) on one of my
worksheets and then inserts a code for that case type in a different
column
on the same worksheet. This criteria is based on a small table located
on
a
different worksheet w/in the same workbook. The formula I tried is
array-entered and is as follows in cell L1 on worksheet "transition":
=INDEX(Codes!$O$25:$O$33,MATCH(1,(Codes!$L$25:$N$3 3=Import!J1:L1),0))
where O25:O33 contains the codes I want to insert (ex: CD1, CD2, etc.)
from
the Codes worksheet, L25:N33 contains the criteria to check against
(Category, Case Type, & Model; respective examples: Residential, ABC,
City;
there are 9 rows in my "key" for each of these.), and J1:L1 represent
the
data to be matched (Category, Case Type, & Model) in the Import
worksheet
(WS). Based on this info, I want Excel to insert the code into L1 (ex:
CD1)
on the "Transition" worksheet.
Also, I would like to add an IF statement to this formula stating that
if
the value in K1 (Case Type) of the Import WS contains BBB, then don't
perform
this match. The reason for this is that BBB cases types can include
multiple
models, some of which are the same as other case types. BBB remains
the
same
code (BBB) for all the different models. Currently the formula is
giving
me
#N/A as an answer instead of CD1 for cell L1. What am I doing wrong?
|