AnswerBun.com

Complex Nested If, AND, Or Function

Super User Asked on January 12, 2021

I must admit, I am not a trained Excel user but I am trying to learn it via the online resources such as yours. Here’s the problem I am hoping to solve in Excel.

I have two columns with text values in 9 rows each, say A,B,C,D,E,F,G,H,I,J,K,L, (Column A), and 1,2,3,4,5,6,7,8,9,10,11,12 (Column B). There are multiple combinations, A AND 1 gives High result, Or A AND 7 gives Low result. Up to this point, I have no problem with this statement:

=IF(OR(AND(A1="A",B1="1"),AND(A1="A",C2="7")),"High","Low")
  1. However, there is a possibility that Neither A AND 1 or A AND 7 come into the picture. And when this does not happen, I would like it leave the cell blank.

  2. Another complexity is that I want to nest more arguments, such as

    =IF(OR(AND(A1="B",B1="2"),AND(A1="B",C2="8")),"High","Low") 
    =IF(OR(AND(A1="C",B1="3"),AND(A1="C",C2="9")),"High","Low")
    =IF(OR(AND(A1="D",B1="4"),AND(A1="D",C2="10")),"High","Low")
    =IF(OR(AND(A1="E",B1="5"),AND(A1="E",C2="11")),"High","Low")
    =IF(OR(AND(A1="F",B1="6"),AND(A1="F",C2="12")),"High","Low")
    =IF(OR(AND(A1="G",B1="7"),AND(A1="G",C2="1")),"High","Low")
    

and so on.

What would be the most efficient solution to this?

One Answer

For the example data you have posted I think this formula works:

=IF(B1 = MOD(6 + MOD(COLUMN(INDIRECT(A1 & 1)),12),12),"High",IF(COLUMN(INDIRECT(A1 & 1))=B1,"Low",""))

Answered by Justin Doward on January 12, 2021

Add your own answers!

Related Questions

Multistep ssh – is it possible?

3  Asked on December 22, 2020 by vahagn-tumanyan

 

Microsoft Word: Table of Figures Ordering

2  Asked on December 21, 2020 by scoopsahaagendazs

 

not able to open my downloads through terminal in Mac OS

0  Asked on December 21, 2020 by daiwik-kashyap

   

Chrome high processor usage on Asus Tuf A15

0  Asked on December 20, 2020 by geeky-gurdeep

     

Lost files since renamed folder

1  Asked on December 20, 2020 by asklep

 

Renaming disks: from D: to F:

1  Asked on December 20, 2020 by user2925716

       

Windows 10 Taskbar Icons Blinking (Compressing)

1  Asked on December 19, 2020 by guy-thomas

     

Mysql Workbench import CSV 0 record imported

1  Asked on December 19, 2020 by zhenyu

   

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir