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

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

    Teden2020 

    Most probably you didn't define named ranges

    Select G2:G4 and enter the name as above.

    You may add/edit/delete the in Name Manager as well

     

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

      SergeiBaklan Ah - I kind of did but I missed ParMin - not sure how. It now all works perfectly. This is so useful to me and my workplace. Thanks so much. I'll leave you in peace now!

       

      Tim

Resources