How to print out matching names?

[Tom]

I have two files each holds a long list of names and other personal
information.
The names are located in Column A. How can I compare the list of names and
ask Excel to print out on Column B of File2 the word "match" if an exact
match is found, but nothing if it is not an exact match, like:

File1 File2
Down Down match
Downes
Downer

Thank you for your help.

Regards,
Tom

 

[Guest]

Hi Tom,

Assuming that the list of names is in Book1 on sheet 1 from A1 down to
whatever row and the other list is in Book2 from A1 down to whatever row. Put
the following formula on sheet 2 in cell B1.

Note: The formula is actually one line but it breaks in half here.

=IF(ISERROR(VLOOKUP(A1,'[Book1
vlookup.xlsm]Sheet1'!A$1:A$26,1,FALSE)),"","Match")

Copy the formula down column B as far as the bottom of the data in column A.

Regards,

OssieMac

 

[Bill Renaud]

If you don't have column labels at the top of your data, insert rows and
add the column label "Name".
Assuming Book1 is the reference list, and Book2 is the data workbook
where the formula will be added, sort Book2 by Name first, then enter
the following formula in cell B2 and fill down:

=IF(A2=A1,B1,IF(ISNA(MATCH(A2,[Book1]Sheet1!$A$2:$A$100,0)),"","Match"))

Column B checks to see if the Name in column A is the same one as the
row immediately above. If so, then the formula sets the value in column
B to the same as the row above. This greatly reduces the number of calls
to the MATCH function, thus speeding up recalculation.

 

[Tom]

My thanks to OssieMac and Bill Renaud. Much appreciate both your help.

Regards,
Tom