Finding entries in a list that don’t exist in another list is a common problem. Let’s say that you have two lists of names on Sheet1 and Sheet2. You want to know which names on Sheet1 are NOT on Sheet2 and vice versa.
The fourth argument in a VLOOKUP function, when set to FALSE, will find an exact match. If it does not find a match, it will return and error (#N/A). You can use this to test one list against another. In the above example, you might use a VLOOKUP next to both lists and look for #N/A, indicating no match was found.
In the above screen shot, the list of names on Sheet1 (on the left) has a column of formulas next to it. The formulas search the list on Sheet2 (on the right) for a match. You can see that Joe is on Sheet1, but not on Sheet2. Column B on Sheet2 has a similar formula which shows that Betty exists on Sheet2, but not on Sheet1.
You can further customize this VLOOKUP formula to only show those entries that don’t match. The ISNA function can be used to determine if VLOOKUP returns an error. The formula on Sheet1 might look like this
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$8,1,FALSE)),"No match","")
If the VLOOKUP returns #N/A, the text “No match” is displayed. Otherwise, an empty string is put in the cell which makes it look blank.
Hi Dick!
Understand you’re demo-ing VLOOKUP, but instead of
=IF(ISNA(VLOOKUP(A1,Sheet2!$A$1:$A$8,1,FALSE)),”No match”,””)
I’d recommend using
=IF(ISNA(MATCH(A1,Sheet2!A:A,FALSE)),”No match”,””)
Or the shorter (and perhaps more efficient since it has fewer function calls, though I’ve never benchmarked it):
=IF(COUNTIF(A1,Sheet2!A:A),””,”No match”)
J.E., the COUNTIF() version is slower than the MATCH() version, at least that’s what FastExcel says !
http://www.mrexcel.com/board2/viewtopic.php?t=40233
As noted there, it’s best to use ISNUMBER(MATCH()) or ISNA(MATCH())
John – MATCH is the better choice. I’m a VLOOKUP junkie, which is a failing of mine. Let’s see, we’re trying to match two columns and there’s a function named MATCH. Darn, that’s intuitive. Thanks for the comment.