Nested If Statements (Multiple)

[Excel2021]

Hello,

I am having issues getting a complex IF statement working. I have three sheets: "Summary", "Filtered COGS Data" & "Filtered Sales Data". To simplify assume That the "Summary" sheet has 2 columns named "Identifier" and "Customer/Vendor" (Columns A-B) and the other sheets have 3 columns named "Identifier", "Customer" and "Vendor" in columns A-C. The data in these columns are stored from A2:B9 (Headers A1-B1) on the "Summary" and A2:C9 (Headers A1-C1) on the other sheets. I am trying to use IF statements and Xlookups from the "Summary" sheet to both the "Filtered COGS Data" & "Filtered Sales Data" using the "Identifier columns on each sheet (Lookup value will be the identifier column on the summary sheet). The customer or vendor can appear in 1 of the 4 columns across the "Filtered COGS Data" & "Filtered Sales Data" sheets depending on the ERP system data. I am trying to pull whichever column has this into the "Customer/Vendor" column on the summary sheet by eliminating the remaining columns using checks to ensure they <> "" or <>0. My formula which does not work is as follows:

=IF($A2="","",
IF(IF(AND(XLOOKUP($A2,'Filtered COGS Data'!$A$2:$A$9,'Filtered COGS Data'!$B$2:$B$9,0,0,1)<>0,XLOOKUP($A2,'Filtered COGS Data'!$A$2:$A$9,'Filtered COGS Data'!$B$2:$B$9,0,0,1)<>""),XLOOKUP($A2,'Filtered COGS Data'!$A$2:$A$9,'Filtered COGS Data'!$B$2:$B$9,0,0,1),
IF(IF(AND(XLOOKUP($A2,'Filtered Sales Data'!$A$2:$A$9,'Filtered Sales Data'!$B$2:$B$9,0,0,1)<>0,XLOOKUP($A2,'Filtered Sales Data'!$A$2:$A$9,'Filtered Sales Data'!$B$2:$B$9,0,0,1)<>""),XLOOKUP($A2,'Filtered Sales Data'!$A$2:$A$9,'Filtered Sales Data'!$B$2:$B$9,0,0,1),
IF(IF(AND(XLOOKUP($A2,'Filtered Sales Data'!$A$2:$A$9,'Filtered Sales Data'!$C$2:$C$9,0,0,1)<>0,XLOOKUP($A2,'Filtered Sales Data'!$A$2:$A$9,'Filtered Sales Data'!$C$2:$C$9,0,0,1)<>""),XLOOKUP($A2,'Filtered Sales Data'!$A$2:$A$9,'Filtered Sales Data'!$C$2:$C$9,0,0,1),
IF(IF(AND(XLOOKUP($A2,'Filtered COGS Data'!$A$2:$A$9,'Filtered COGS Data'!$C$2:$C$9,0,0,1)<>0,XLOOKUP($A2,'Filtered COGS Data'!$A$2:$A$9,'Filtered COGS Data'!$C$2:$C$9,0,0,1)<>""),XLOOKUP($A2,'Filtered COGS Data'!$A$2:$A$9,'Filtered COGS Data'!$C$2:$C$9,0,0,1),"")))))))))

Thanks

 

[dreid1011]

Glad you got it figured out.