Something better than OR()

[Lee]

=IF(B2="misc",E2,IF(OR(B2*1=I$2,B2*1=I$3,B2*1=I$4,B2*1=I$5,B2*1=I$6,B2*1=I$7,B2*1=I$8,B2*1=I$9,B2*1=I$10,B2*1=I$11,B2*1=I$12,B2*1=I$13,B2*1=I$14,B2*1=I$15,B2*1=I$16,B2*1=I$17,B2*1=I$18,B2*1=I$19,B2*1=I$18,B2*1=I$19,B2*1=I$20,B2*1=I$21,B2*1=I$22,B2*1=I$23),E2,E2*20/18))

Is there a better way to do the above formula? I have experimented with an
array but can't get it to work.
Thanks for any help or suggestions.
Lee

 

[Don Guillett]

Try incorporating MATCH into your formula.

 

[T. Valko]

Try this:

=IF(B2="misc",E2,IF(ISNUMBER(MATCH(B2*1,I$2:I$23,0)),E2,E2*20/18))

 

[Lee]

Thanks, it works! I don't understand the isnumber part but will read more in
the help file.
Thanks again.
Lee

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[Bob Phillips]

ISNUMBER just checks whether the MATCH is working or not. If MATCH finds the
value, it returns its position in the array, the index number. If it
doesn't, it returns #N/A, so ISNUMBER returns TRUE if found, FALSE if not.

 

[Brian]

I have not modified the part below where it quotes the original message. It
is useless. I see a blank message posted by "unknown" at "10/18/2009 3:25 AM
PST".

Compare that with the message by "Bob Phillips" at "10/19/2009 3:50 PM PST",
which quotes three previous messages. Suddenly the subject line makes sense!

What's going on here?