Forum Discussion

Copper Contributor

Sep 22, 2020

Solved

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

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 returnin...

excel

Formulas and Functions

Rajesh_Sinha

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")

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.

UKBlueSteel
Copper Contributor
Sep 23, 2020
Very interesting, thanks Rajesh. I learnt a lot from your post, very invaluable information.

I did wonder about case and non case sensitive functions.
- Rajesh_Sinha
  Steel Contributor
  Sep 30, 2020
  Glad to help you,, keep asking ☺
- Rajesh_Sinha
  Steel Contributor
  Sep 24, 2020
  Glad to help you,,, in case you are satisfied with my post then you may mark it as best answer/post. Keep asking ☺
  - UKBlueSteel
    Copper Contributor
    Sep 29, 2020
    Hi RajeshRajesh_Sinha
    
    I was wondering if I had several columns with different metals in that has "Y" in, say
    Columns F2-I2 and I wanted a formulat for column E that gives a Y if there is just one Y in any of those columns but if there is a N in column J2 it gives a "N" that trumps any "Y" value?
    
    Thank You 🙂