Forum Discussion

Teden2020's avatar
Teden2020
Copper Contributor
Feb 10, 2020
Solved

Help with IF this but IF that...

My columns are as below. I want it to give me a YES in column 3 if the first column is "I" and the second column is >4 and <7. Success with =IF(AND(C2="I",D2>4,D2<7),"YES","NO").   BUT some of the ...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 10, 2020

    Teden2020 

    As variant

    =IF(OR(
     AND(C2="I",D2>4,D2<7),
     AND(C2="A",D2>7,D2<10)
   ),"YES","NO")
Teden2020's avatar
Teden2020
Copper Contributor
Feb 10, 2020

SergeiBaklan Can I pick your brains further? What if didn't want just a simple NO? For example for an "I" the range should be within 4 and 7. Is there way you can get a "YES" for that range, a "LOW" if the number is too low and a "HIGH" if the number is too high? Getting too complicated?

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 10, 2020

Teden2020 

That could be

=IF(C2="I",IF(D2>7,"High",IF(D2>4,"Yes","Low")),"No")

or you'd like to combine with C2="A" in the same formula?

  • Teden2020's avatar
    Teden2020
    Copper Contributor
    Feb 10, 2020

    SergeiBaklan Excellent. Actually yes and is now the time to tell you there's an "F" too?!

     

    A should be  >7  <10

    I should be   >4   <7

    F should be  >0   <4

     

    Thanks for this!

     

    Tim

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 10, 2020

      Teden2020 

      Tim, with this it's better not to hardcode all combinations, but create helper range somewhere, even on another sheet (you could hide it) like this

      and give names to all 3 columns of this range. Here are ParName, ParMin and ParMax. Formula in E2 will be

      =IFNA(
  IF(D2>INDEX(ParMax, MATCH(C2,ParName,0)),
     "High",
     IF(D2>INDEX(ParMin,MATCH(C2,ParName,0)),
        "Yes","Low")
   ),
"No")

      and drag it down.

      • Teden2020's avatar
        Teden2020
        Copper Contributor
        Feb 11, 2020

        SergeiBaklan Many, many thanks for your continued support - I'm learning a great deal.

         

        I can't quite get the last (and most ideal solution) to work.

         

        Here is what I have set up:

         

        Target Art AWOLArt FFT (Y7)MATCH? ParMinMax
        I5.5#NAME? A710
        A7.2YES I47
        I4.8YES F04

         

        If I select the three descending cells in the far right columns I get the right title (e.g. I select A I F and get ParName).

         

        The function I have put in the cell which results in #NAME? is =IFNA(IF(D2>INDEX(ParMax,MATCH(C2,ParName,0)),"High",IF(D2>INDEX(ParMin,MATCH(C2,ParName,0)),"Yes","Low")),"No")

         

        Can you see something obvious I might have missed? This will honestly be so useful for me in my professional role.

         

        Thanks,

        Tim

Resources