I have several cells in a row that have Y or N in them, I want to enter a formula in a column for when one of those cells in a row is "Y" to return a "Y" value.Also I have in those cells returning a "Y" value if say "Steel" or "Copper" is in a product name in column A. I wondered how to create a formula that gives a "Y" value if a cell name contains "Steel" or "Copper" or "Nickel".Thirdly, how can I return a "Y" value for a product name in column A having "Ni" or "Cu" in the name on its own without returning a false positive for say "Continiti" or "Occupose" etc. but for say "Product C Ni Cu Plate"Many Thanks

UKBlueSteel ,,, For Row has Y & N:=IF(COUNTIF($A$2:$E$2,"Y")>0,"Y","N") Or to make the formula dyanamic,, you can use this even: =IF(COUNTIF($A$2:$E$2,A1)>0,"Y","N") Where A1 has Y, or you may put other alphaet to test.To check the products:=IF(OR(A1={"Steel","Cooper","Nickel"}),"Y","N") For Ni and others: =IF(OR(A1={"St","Co","Ni"}),"Y","N")

Greetings, I hope you are well. I just need some formula help.

18 Replies

Rajesh_Sinha

Steel Contributor

Sep 23, 2020

UKBlueSteel ,,,

For Row has Y & N:

=IF(COUNTIF($A$2:$E$2,"Y")>0,"Y","N")

Or to make the formula dyanamic,, you can use this even:

=IF(COUNTIF($A$2:$E$2,A1)>0,"Y","N")

Where A1 has Y, or you may put other alphaet to test.

To check the products:

=IF(OR(A1={"Steel","Cooper","Nickel"}),"Y","N")

For Ni and others:

=IF(OR(A1={"St","Co","Ni"}),"Y","N")

SergeiBaklan
Diamond Contributor
Sep 22, 2020
UKBlueSteel

As variants

1) for columns from A to C

=IF(SUMPRODUCT(--(A1:C1="Y")),"Y","N")

2) For A1

=IF(SUM(COUNTIF(A1,"*"&{"Steel","Cooper","Nickel"}&"*")),"Y","N")

3) same as above if only take " Ni " and " Cu ". If spaces around is not the case, when it shall be another formal logic.
- UKBlueSteel
  Copper Contributor
  Sep 23, 2020
  Thank you so much, I was googling and searching and trying for a while to do this, this really helped me a lot.
  
  I was using =IF(IS NUMBER(SEARCH("Nickel",A2)),"Y","N")
  
  It wasn't as good as your formulas which solved the problems I was having.
  
  Really appreciate the help and the interest from others in the community. Thank you Sergei
  
  Don
  - Rajesh_Sinha
    Steel Contributor
    Sep 23, 2020
    UKBlueSteel ,,,
    
    Let me point out the mistake in the formula you have used,
    
    =IF(IS NUMBER(SEARCH("Nickel",A2)),"Y","N")
    
    Space is not allowed between IS & Number,,, since ISNUMBER is reserve word or a Function.
    So use it as,,
    
    =IF(ISNUMBER(SEARCH("Nickel",A2)),"Y","N") , will works.
    
    Not let me say about combination of ISNUMBER & SEARCH.
    
    ISNUMBER returns TRUE for numbers, and FALSE for anything else.
    SEARCH finds the text/substring , and returns it's position as a number.
    Where ISNUMBER returns TRUE, if finds text at the position.
    If SEARCH doesn't finds the text/substring, returns a #VALUE! error, which causes ISNUMBER to return FALSE.
    
    Note: In your case since formula tests more than one product at a time, so looks useless, but if you alter the formula will work.
    
    =IF(ISNUMBER(SEARCH(E1,A1)),"Y","N")
    
    Where E1 has either Nickel or Copper or Steel (use drop down in cell E1 to make it more effective).
    You can replace SEARCH with FIND, but remember, SEARCH is case-insensitive, while FIND is case-sensitive.
ExcelExciting
MVP
Sep 22, 2020
HI UKBlueSteel

It would be great if you can attach sample file along with your desired output enter manually

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert
If you find the above solution resolved your query don't forget mark as Official/Best Answer to help the other members find it more

Forum Discussion

Greetings, I hope you are well. I just need some formula help.