Create a cross refference formula that meets multiple criterias

Bobby1

I am looking to Create a cross-sheet reference formula that meets multiple criteria.

see screenshot

Kaveri Vipat

Hi Bobby1,

Can you please share more details what you want to achive. As I see, I think the first IF Condition is fulfilled so it is showing the Result.

Thank You

Kaveri Vipat, Smartsheet Engineer, Ignatiuz Software

Bobby1

the problem i have is that there are 2 spreadsheets used by 2 teams, but both should have the same info. in the screenshot, i displayed the different scenarios. I can keep it simple and have returned a MATCH or NO MATCH. The goal is to have the formula return with a value that indicates where the discrepancy lies.

if sheet B cell is blank but sheet A is not. return sheet B- this way the team knows where to focus their attention

If sheet A is blank but sheet B is not, return the sheet A-this way the team knows where to focus their attention

if both Sheet A & Sheet B is not blank but do not match, return No Match.

If Sheet A & Sheet B has a match, return Match

SmartLew

Hi @Bobby1

This is the formula you want:

=IF(AND([Sheet A]@row = "", [Sheet B]@row <> ""), "Sheet A", IF(AND([Sheet A]@row <> "", [Sheet B]@row = ""), "Sheet B", IF(AND([Sheet A]@row <> "", [Sheet B]@row <> "", [Sheet A]@row <> [Sheet B]@row), "NO MATCH", IF(AND([Sheet A]@row <> "", [Sheet B]@row <> "", [Sheet A]@row = [Sheet B]@row), "MATCH"))))

Bobby1

Thank you @SmartLew i replaced sheet a/b with the actual names but return unparsable

=IF(AND([Invoice Date]@row = "", {v4. MAT Date} <> ""), "Sheet A", IF(AND([Invoice Date]@row <> "", {v4. MAT Date}= ""), "Sheet B", IF(AND([Invoice Date]@row <> "", {v4. MAT Date} <> "", [Invoice Date]@row <> {v4. MAT Date}, "NO MATCH", IF(AND([Sheet A]@row <> "", {v4. MAT Date} <> "", [Invoice Date]@row = {v4. MAT Date}), "MATCH"))))

SmartLew

you are combing cells and ranges there. That wont work.

I thought you were trying to compare the two columns labeled Sheet A and Sheet B in your screen shot, with the formula in the column "Results"

Is that not the case?

A little confused on what you are trying to achieve now; apologies.