Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Or Statement, Help!
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Or Statement, Help!
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)) "Walter" wrote: 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
IF Or Statement, Help!
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(B 2="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={"1 23W","456W","789W","012W"}),$J$8,IF(B2="123A",$J$1 2,IF(OR(B2={"111A","222A","333A"}),$J$13,"")))))) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Walter" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SUMIF statement with AND statement | Excel Discussion (Misc queries) | |||
Can an If statement answer an If statement? | Excel Discussion (Misc queries) | |||
appending and IF statement to an existing IF statement | Excel Worksheet Functions | |||
If statement and Isblank statement | Excel Worksheet Functions | |||
Help please, IF statement/SUMIF statement | Excel Worksheet Functions |