TransWikia.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!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP