Summary:
I am looking to improve the efficiency of / totally rewrite the formula I have written to compare two different data sets, the output of which will change based on a third data set. This is done 6 times within one statement. My current method works, but I have no doubt there are better ways of doing it, but I'm not sure where to start.
Use Example:
If A1="NO", I want to search my 6 datasets and find the first FALSE result in C1, F1, I1, L1, O1 or R1 regardless of the content in cells D1,F1,J1,M1,P1 or S1, then find out which dataset that FALSE is in.
If A1="YES", I want to search my 6 datasets and find the first FALSE result in C1, F1, I1, L1, O1 or R1 as long as the right adjacent cell has a date before 2012.
Example table with above criteria, with the correct output in FORMULAHERE:
| PRE2012 | Data1 | Data1Invalid? | Data1 Date | Data2 | Data2Invalid? | Data2 Date | Data3 | Data3Invalid? | Data3 Date | Data4 | Data4Invalid? | Data4 Date | Data5 | Data5Invalid? | Data5 Date | Data6 | Data6Invalid? | Data6 Date | FORMULAHERE |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| NO | AAA | TRUE | 01/01/2020 | BBB | FALSE | 01/01/2018 | CCC | TRUE | 01/01/2015 | DDD | TRUE | 01/01/2013 | EEE | TRUE | 01/01/2010 | FFF | FALSE | 01/01/2009 | "USE DATA 2" |
| NO | 111 | FALSE | 01/01/2020 | 222 | FALSE | 01/01/2018 | 333 | TRUE | 01/01/2015 | 444 | TRUE | 01/01/2013 | 555 | TRUE | 01/01/2010 | 666 | FALSE | 01/01/2009 | "USE DATA 1" |
| YES | ABC | TRUE | 01/01/2020 | DEF | FALSE | 01/01/2018 | GHI | TRUE | 01/01/2015 | JKL | TRUE | 01/01/2013 | MNO | TRUE | 01/01/2010 | PQR | FALSE | 01/01/2009 | "USE DATA 6" |
| YES | 123 | TRUE | 01/01/2020 | 456 | FALSE | 01/01/2018 | 789 | TRUE | 01/01/2015 | 012 | TRUE | 01/01/2013 | 345 | FALSE | 01/01/2010 | 678 | FALSE | 01/01/2009 | "USE DATA 5" |
Current solution:
Currently, I have the functionality working as I need by using the following excruciatingly painful =IF statement:
=IF(A1="Yes",(IF(AND(D1<DATE(2012,1,1),C1=FALSE),"Address 1",IF(A1="Yes",IF(AND(G1<DATE(2012,1,1),F1=FALSE),"Use Data 2",IF(A1="Yes",IF(AND(J1<DATE(2012,1,1),I1=FALSE),"Use Data 3",IF(A1="Yes",IF(AND(M1<DATE(2012,1,1),L1=FALSE),"Use Data 4",IF(A1="Yes",IF(AND(P1<DATE(2012,1,1),O1=FALSE),"Use Data 5",IF(A1="Yes",IF(AND(S1<DATE(2012,1,1),R1=FALSE),"Use Data 6",IF(A1="Yes",IF(R1=TRUE,"All Invalid",IF(A1="Yes",IF(AND(D1>DATE(2012,1,1),G1>DATE(2012,1,1),J1>DATE(2012,1,1),M1>DATE(2012,1,1),P1>DATE(2012,1,1),S1>DATE(2012,1,1)),"No Pre-2012 data on file","Review Manually")))))))))))))))),IF(A1="No",IF(C1=FALSE,"Address 1",IF(F1=FALSE,"Use Data 2",IF(I1=FALSE,"Use Data 3",IF(L1=FALSE,"Use Data 4",IF(O1=FALSE,"Use Data 5",IF(R1=FALSE,"Use Data 6",IF(R1=TRUE,"All Invalid","Review Manually.")))))))))
What would be the best way to achieve what I am trying to? I am hoping to learn, so any further reading to help me understand an answer would be much appreciated, if possible. Thanks!
Using MSO365. Thanks!
CodePudding user response：
What about including IFS() with relative ranges in XLOOKUP():
=XLOOKUP(1,(C2:R2=FALSE)*(IFS(A2="NO",1,A2="YES",D2:S2<DATE(2012,1,1))),"Use "&B$1:Q$1,"",0)
Note that you could replace "" with any value you'd like to show if no value is found.