Look up or match a name or license number in two different worksheets

[mixedbagqueen]

Hi,

I have a spreadsheet containing the names and license numbers of 400 people...let's say, employees; then I have another spreadsheet with the names and license numbers of 300 individuals whose licenses have been canceled. I need to compare the two lists to see if any employees are among the list of canceled licenses. I'd appreciate any recommendations for what function to use for this.

Thanks!
Queen

[JohnTopley]

You could use MATCH to compare your 400 with the 300

For example

in B2 ...

=IFERROR(MATCH(A2,Sheet2!A2:A300,0),"Matched",""))

Matching A2 on Sheet1 with list on Sheet2 in Column A

Copy formula down

[mixedbagqueen]

I tried the formula, but something went wrong. In case more specific information would be helpful, in Sheet 1, I have license numbers in Column A, rows 2 through 346, and in Sheet 2, I have license numbers in Column A, rows 2 through 386.

I need to know if any of the license numbers listed in Sheet 1 A2:A346 are also listed in Sheet 2, A2:A386

Thanks for any help!

[JohnTopley]

Try

=IFERROR(IF(MATCH(A2,Sheet2!$A$2:$A$315,0),"Matched",""),"")

[FlameRetired]

Another way. Array-entered in C2 of the attached and filled down until you get blanks.

Please Login or Register  to view this content.

If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

You can adjust the INDEX range to include the names. I just "dummied" the numbers.