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","") ' OR =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))
Originally Posted on January 6, 2015
Last Updated on August 15, 2019
Last Updated on August 15, 2019
All information on this site is shared with the intention to help. Before any source code or program is ran on a production (non-development) system it is suggested you test it and fully understand what it is doing not just what it appears it is doing. I accept no responsibility for any damage you may do with this code.