Excel – VLookup – Compare Two Columns/Sheets

Sometimes we need to compare lists in Excel, this function will assist in getting a clean comparison.

NOTE: It is best when comparing that all columns are same format (ie. Text, Number, etc.)

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$A$3,1,FALSE)),"MISSING FROM OTHER SHEET","")

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A$1:$A$3,1,FALSE)),"MISSING FROM OTHER SHEET","")

OR

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A:A,1,FALSE)),"No","Yes")

=IF(ISERROR(VLOOKUP(A1,Sheet1!$A:A,1,FALSE)),"No","Yes")

Going one step further, if you find a match and need a value from the other worksheet you can do this.
In this example the 2 grabs the value from Worksheet2!B

=IF(ISERROR(MATCH(B3,Worksheet2!A:A, 0)), "No Match", VLOOKUP(B3,Worksheet2!A:B,2,FALSE))

=IF(ISERROR(MATCH(B3,Worksheet2!A:A, 0)), "No Match", VLOOKUP(B3,Worksheet2!A:B,2,FALSE))