IF Or Statement, Help!

[Walter]

Is this how you set up an IF OR statement for Excel?

=IF(B2="123U" or "12B3",$J$2,IF(B2="123N",$J$4,IF(B2="123T" or "345T" or
"678T",$J$5,IF(B2="123W" or "456W" or "789W" or
"012W",$J$8,IF(B2="123A",$J$12,IF(B2="111A" or "222A" or "333A",$J$13,""))))))

I have a bunch of prefixes in column B that I want the function to search
for and if found then return a set text from column J. My statement does not
work when I fill column D with it.

 

[Duke Carey]

Put all of these prefixes in a vertical list, i.e., in a column. To the
right of each prefix put the value that corresponds to that prefix. Let's
say that range is m1:n10

No use a formula along the lines of

=if(isna(vlookup(b2,$m$1:$n$10,2,0)),"", vlookup(b2,$m$1:$n$10,2,0))

 

[Sandy Mann]

Try:

=IF(OR(B2="123U",B2="12B3"),$J$2,IF(B2="123N",$J$4,IF(OR(B2="123T",B2="345T",B2="678T"),$J$5,IF(OR(B2="123W",B2="456W",B2="789W",B2="012W"),$J$8,IF(B2="123A",$J$12,IF(OR(B2="111A",B2="222A",B2="333A"),$J$13,""))))))

or slightly shorter:

=IF(OR(B2={"123U","12B3"}),$J$2,IF(B2="123N",$J$4,IF(OR(B2={"123T","345T","678T"}),$J$5,IF(OR(B2={"123W","456W","789W","012W"}),$J$8,IF(B2="123A",$J$12,IF(OR(B2={"111A","222A","333A"}),$J$13,""))))))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk