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.
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

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,340
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,089,449
Messages
5,408,283
Members
403,195
Latest member
biafrapl

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top