R
RS
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$33=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?
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$33=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?