Comparing 3 or more columns to find matches

Greetings forumers,

I have a question on how to compare three columns to gain exact match.
Here's an example

Column A
348
B83
3248B

Column B
3248B
B83
348

Column C
348
B83
3248B

How do I exactly know which row has the match value? I sort the column A and B using method =IF(A<>B,"OK","Fail") but I don't know how to implement it to 3 or more columns? Help is appreciated :D Thank you.

Try this formula D1

=IF(A1=B1,IF(B1=C1,"yes","no"),"no")

Or:

=IF(AND(A1=B1,B1=C1),"OK","Fail")

I prefer jason's solution.

Hmm.. In addition, how do I find the exact match location for the third column?
Let say

The result "Fail - 384" which 384 indicates the correct column
Any help on that? Thanks :D the solution just works great, but I need to know if the match is in another column, I know which column it is..

Ok, so if there is a match in columns 1 and 2, then you want to know the matching row number in the 3rd column? If that is correct, try:

=IF(A1=B1,IF(B1=C1,"OK","Fail - "&MATCH(A1,C:C,0)),"Fail")

HTH

Jason

This formula

=IF(A1=B1,IF(B1=C1,"OK","Fail - "&MATCH(A1,C:C,0)),"Fail")

works for some parts, but when the first, second or third column has a blank data, it results in OK rather than FAIL. Other than that, how exactly the &MATCH(A1,C:C,0) works? I don't know how to change the C:C section.
I tried this

=IF(F14=BU14,IF(BU14=EB14,"OK","Fail - "&MATCH(F14,EB:EB,0)),"Fail")

which EB:EB is the third column and when the third column doesn't match the two others, it will result in FAIL (which is correct) but the number shown is always -1 of its current datas' number

I have attached an examples, hopefully someone can correct my mistake, thanks

See if this works:

=IF(AND(F14="",BU14="",EB14=""),"Fail",IF(F14=BU14,IF(BU14=EB14,"OK","Fail - "&IF(ISNA(MATCH(F14,EB:EB,0)),"No match found",MATCH(F14,EB:EB,0))),"Fail"))

With Match(A1, C:C, 0), it is looking to find the cell number in column C that exactly matches cell A1. The Help files are good at explaining the worksheet functions.

HTH

Jason