Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested if with "wild card"
Would really appreciate it if you can help with the following nested if
example: Example: A1 B1 Large 2009 100 Medium 2009 200 Medium 2008 500 Large 2008 300 Other 2009 400 I want to set up a nested if formula to pick up the value in B1 if there is the word "Large" in A1 or if there's "Medium" in A1, else enter zero. I was going with this but it didn't work: =if(or(A1="Large*",A1="Medium*"),B1,0) Can someone help? Thank you. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested if with "wild card"
something like
=IF(OR(LEFT(A1,4)="Larg",LEFT(A1,4)="medi"),B1,0) -- Greetings from New Zealand "mldancing" wrote in message ... Would really appreciate it if you can help with the following nested if example: Example: A1 B1 Large 2009 100 Medium 2009 200 Medium 2008 500 Large 2008 300 Other 2009 400 I want to set up a nested if formula to pick up the value in B1 if there is the word "Large" in A1 or if there's "Medium" in A1, else enter zero. I was going with this but it didn't work: =if(or(A1="Large*",A1="Medium*"),B1,0) Can someone help? Thank you. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested if with "wild card"
Thank you for you reply. In this example this formula would work.
Is it possible to use a wild card? 'cause there could be other combination of description such as Extra Large or Medium Small. Thank you. "Bill Kuunders" wrote: something like =IF(OR(LEFT(A1,4)="Larg",LEFT(A1,4)="medi"),B1,0) -- Greetings from New Zealand "mldancing" wrote in message ... Would really appreciate it if you can help with the following nested if example: Example: A1 B1 Large 2009 100 Medium 2009 200 Medium 2008 500 Large 2008 300 Other 2009 400 I want to set up a nested if formula to pick up the value in B1 if there is the word "Large" in A1 or if there's "Medium" in A1, else enter zero. I was going with this but it didn't work: =if(or(A1="Large*",A1="Medium*"),B1,0) Can someone help? Thank you. |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested if with "wild card"
You can't use a wildcard with comparison operators like =, try using COUNTIF =IF(SUM(COUNTIF(A1,{"*large*","*medium*"})),B1,"") regards, barry -- barry houdini ------------------------------------------------------------------------ barry houdini's Profile: http://www.thecodecage.com/forumz/member.php?userid=72 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=135071 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Nested if with "wild card"
Bill's formula only looks at the first 4 characters in the cell (=left(a1,4)).
So you don't need a wild card. mldancing wrote: Thank you for you reply. In this example this formula would work. Is it possible to use a wild card? 'cause there could be other combination of description such as Extra Large or Medium Small. Thank you. "Bill Kuunders" wrote: something like =IF(OR(LEFT(A1,4)="Larg",LEFT(A1,4)="medi"),B1,0) -- Greetings from New Zealand "mldancing" wrote in message ... Would really appreciate it if you can help with the following nested if example: Example: A1 B1 Large 2009 100 Medium 2009 200 Medium 2008 500 Large 2008 300 Other 2009 400 I want to set up a nested if formula to pick up the value in B1 if there is the word "Large" in A1 or if there's "Medium" in A1, else enter zero. I was going with this but it didn't work: =if(or(A1="Large*",A1="Medium*"),B1,0) Can someone help? Thank you. -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
change "true" and "false" to "availble" and "out of stock" | Excel Worksheet Functions | |||
trying to do custom autofilter but its not recognising "*"as wild | Excel Discussion (Misc queries) | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel | |||
Wild Characters in "IF" function | Excel Discussion (Misc queries) |