IF + OR formula

amp7394

New Member
Joined
Nov 16, 2018
Messages
8
What would be the correct formula for IF a cell contains text x OR contains text Y, then give me Z value. Right now I have:

=IF(SUM(COUNTIF(A6,{"BSC-NCORP-A","BSC-CORP-A"})),"BSC","CIG")

But I am only getting the CIG value for everything.
 

Aryatect

Active Member
Joined
Jun 17, 2019
Messages
308
Hi, I think below should work:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">BSC-NCORP-A</td><td style=";">BSC</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">BSC-NCORP-B</td><td style=";">BSC</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">BSC-NCORP-C</td><td style=";">CIG</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet4</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">B5</th><td style="text-align:left">=IF(<font color="Blue">OR(<font color="Red">A5="BSC-NCORP-A",A5="BSC-NCORP-B"</font>),"BSC","CIG"</font>)</td></tr></tbody></table></td></tr></table><br />
 

amp7394

New Member
Joined
Nov 16, 2018
Messages
8
Hi, thank you for your response. I am actually only asking Excel to look for a portion of the cell. The entire A5 cell says:
BSC-NCORP-A|PLT-NA-NA|CIG-NA-NA|GLD-NCORP-L

So, IF A5 contains BSC-NCORP-A or BSC-CORP-A, then BSC, if not CIG.

How would I edit the formula so that its only asking to look portion of text in the cell?

<tbody>
</tbody>
 

FormR

MrExcel MVP
Joined
Aug 18, 2011
Messages
6,145
Office Version
365
Platform
Windows
Hi, see below additions in red to you original formula.

=IF(SUM(COUNTIF(A6,"*"&{"BSC-NCORP-A","BSC-CORP-A"}&"*")),"BSC","CIG")
 
Last edited:

Forum statistics

Threads
1,077,686
Messages
5,335,656
Members
399,032
Latest member
thefinu

Some videos you may like

This Week's Hot Topics

Top