Problem with Formula in YouTube video that matches my exact requirment

[ALbert_albie]

https://youtu.be/L_KJE_pyB34
I'm trying to reproduce this formula as used in the YouTube video above ,
I'm trying to catch duplicates across spreadsheets and document their location.
I've been trying for a couple of days and using formula builder to no avail.
The formula I'm trying to reproduce is .
=IFERROR(IF(AND(MATCH($A10,$A:$A,),MATCH(SA10,'OTHERSHEET'!$A:$A,))),"x",),"")

So as in the video if the value in A1 matches a value anywhere else in the workbook it documents it and gives the sheet its present in.
I've tried every possible combination.
All help and suggestions appreciated.

[sandy666]

your formula: =IFERROR(IF(AND(MATCH($A10,$A:$A,),MATCH(SA10,'OTHERSHEET'!$A:$A,))),"x",),"")
correct formula: =IFERROR(IF(AND(MATCH($A10,$A:$A,),MATCH(SA10,OTHERSHEET!$A:$A,)),"x",),"")

the rest we can say if you attach example file (xlsx, or xlsm)

edit:
or change comma to semicolon (it's depend of your local settings)

[FDibbins]

Hi, welcome to the forum

=IFERROR(IF(AND(MATCH($A10,$A:$A,),MATCH(SA10,'OTHERSHEET'!$A:$A,))),"x",),"")

So as in the video if the value in A1 matches a value anywhere else in the workbook it documents it and gives the sheet its present in.

That will not give any sheet names, only that a match exists in both...and really, im not even sure that will do that, because you are not testing MATCH against anything. MATCH returns a number (the row the match was found in) or an error message if no match was found, so if there is a match in both sheets.

So you could have...
=IFERROR(IF(AND(2,6),"x",),"")
where 2 and 6 would be what each mach returned

[ALbert_albie]

Thanks a million, The problem was that I was using "," and when I used ";" it worked fine.
Thanks again.
AL

[sandy666]

You are welcome
If problem is resolved mark thread as solved and add star to person(s) who helped you. Thank you.

[ALbert_albie]

RESOLVED

[sandy666]

Go to your first post, top right side and from Thread Tools select Resolved/Solved