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

"Bob Phillips" wrote:

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)



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