Trying to use INDEX and MATCH to insert a value w/ multiple criter

[Guest]

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?

 

[Bob Phillips]

=IF(K1="BBB",INDEX(Codes!$O$25:$O$33,MATCH(1,(Codes!$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)

 

[Guest]

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]

Yes, the carriage return, or more precisely Alt-Enter, was added to make it
more readable. I find that helps a lot with big formulae. It also helps when
posting to the groups, as the text often gets wrapped around, and it usually
wraps at a point that either makes it hard to read, or worse, breaks the
formula.

Some people add spaces in formula, again for readability, so for instance
they might write

=IF(A1<TODAY(), "Valid value", "Invalid value" & TEXT(A1, "dd-mm-tyyyy"))

Personally, whilst this can sometimes be helpful when debugging a formula, I
like to have no whitspace in my formulae, so I would write

=IF(A1<TODAY(),"Valid value","Invalid value"&TEXT(A1,"dd-mm-tyyyy"))

I don't think it loses anything. If you are anything like me, the biggest
problem is matching parentheses. But you can alwways go overboard and write
your formula like so

=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)
)
)

to try and help. Biggest problem of course is that you cannot paste such a
formula into a cell, as it will span multiple lines, but have to paste into
the formula bar.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Peo Sjoblom]

You can remove one "BBB"

=IF(K1="BBB",K1,INDEX(etc

 

[Guest]

Dear Peo,

Thanks for the tip!

You can remove one "BBB"

=IF(K1="BBB",K1,INDEX(etc

 

[Guest]

Thanks once again for your help & explanations. These forums are great in
helping me become a more knowledgeable Excel user.