I am hoping for help for a new challenge in my veterinary research project. I would like a way to cross check values in different columns in my Excel spreadsheet. I want to make sure that for every case (bird) that has been assigned a category that it has at least one diagnosis (subcategory) for that category; and that no diagnoses were incorrectly assigned to a bird that didn’t have that category assigned. I have 6500 cases (birds), 27 categories, and over 200 diagnoses (subcategories) and so I am hoping for a formula to help save me time and improve accuracy.

So in the table above, there would be two CORRECT conditions in the CROSS-CHECK (output) column: “0” and “1”.

• “0” would be that the particular category was not assigned to the bird (CATEGORY1=”N”) and no diagnosis (subcategory) for that category was assigned (DIAGNOSIS1:DIAGNOSIS3=”N”).
• “1” would be that a category was assigned (CATEGORY1=”Y”) and at least one diagnosis (subcategory) was assigned (at least one column in DIAGNOSIS1:DIAGNOSIS3 contains a “Y”).

There would be two ERROR conditions in the CROSS-CHECK (output) column, “2” and “3”.

• “2” would be that the category was assigned to the bird (CATEGORY1=”Y”), but the bird was not assigned any diagnosis (subcategory) for that category (DIAGNOSIS1:DIAGNOSIS3=”N”)
• “3” would be that the category was not assigned to the bird (CATEGORY1=”N”), but at least one diagnosis (subcategory) was assigned (at least one column in DIAGNOSIS1:DIAGNOSIS3 contains a “Y”)

To add to the challenge, the “N” and “Y” in the CATEGORY and DIAGNOSIS columns have been populated using LAMBDA and BYROW functions.

I have tried several formulas, some including IF(COUNTIF), but can only come up with 2 potential condition (0/1, or Y/N) for the cross-check column. I need a more complex formula and I am having trouble writing it myself. Additionally, because of LAMBDA/BYROW I think, I have to do a hard return to each row in the cross-check column to get it to give the correct result. I do have Microsoft 365.