1

enter image description here

I am trying to compare matching numbers in column AD against eachother, to check if their corresponding numbers in AF match as well. If they do not, I would like an indicator (!!!) in AG to show next to the inconsistent data points, for future use.

If ADx=ADy, but AFx<>AFy, output "!!!" to AGx and AGy.

For example, in the image:

AD14=AD15=AD18. But AF14, AF15, AF18 are not all equal. So AG14, AG15, AG18 = "!!!"

AD21=AD25=AD27. But AF21, AF25, AF27 are not all equal. So AG21, AG25, AG27 = "!!!"

I had made a solution before but after some revamping I forgot it, and I wasn't able to replicate it, and it was hardly an elegant solution in the first place. So hopefully someone who knows what they're doing can help :)

Improve this question

1 Answer 1

Reset to default
2

You may try:

=map(D2:D,lambda(Σ,if(Σ="",,if(countunique(ifna(filter(F:F,D:D=Σ)))>1,"❗",))))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thank you very much! It works perfectly for what I need, and is a lot more efficient than what I had before. Hopefully I'll figure out how to recreate it myself in the future!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.