So in this scenario, you have two lists, and you want to check one list against the other and look for missing values. There could be many situations you could be faced with this when using Excel or Google Sheets; probably most frequently when error-checking.
The way to do this is to use VLOOKUP, either alone or combined with IFERROR, or IF and ISERROR if you want the end result to look a bit more professional.
So to begin with, let’s just start by using VLOOKUP (we have a blog post just about using VLOOKUP, so head on over to there to familiarise yourself [LINK]). The technique will be exactly the same in Excel as it is in Google Sheets.
So here we have List 1 and List 2 – we can clearly see that List 2 is shorter, and so therefore some items from List 1 are missing – but which ones?
Using VLOOKUP alone:
We can start by putting a checking column into column B and using a VLOOKUP formula; we will be telling the formula to return column 1 (i.e. return its own value). You will also notice that I have used dollar signs before the characters of the list 2 range ($D$2:$D$9 instead of just D2:D9) – this is called Absolute Referencing and is a big enough topic for a blog post in itself. For now just know that it needs to be like this when I drag the formula down so it refers to the same block of cells without the formula messing up.
Once you’ve entered the formula, drag it down and you will see that the error message #N/A has appeared next to each list item that is missing from List 2:
This is all well and good for a quick check, and maybe this is all you need. But what if you want to pass this on to another user? They may not understand what the #N/A means in this scenario and assume that there is just a general error with the formula. In other words they may think you are not a Spreadsheets Pro.
Using VLOOKUP combined with IFERROR:
Instead of using the formula we can enhance the formula =VLOOKUP(A2,$D$2:$D$9,1,FALSE) with the IFERROR formula. So in cell B2 we would write:
=IFERROR(VLOOKUP(A2,$D$2:$D$9,1,FALSE),”Missing from List 2″)
and drag this down, now we have something a bit neater looking:
What this formula is doing is saying “if there is an error within the VLOOKUP formula, return ‘Missing from List 2’, otherwise just return the VLOOKUP result”
Using VLOOKUP combined with IF and ISERROR:
The previous example was an improvement, but what we could really do with is a check formula that says whether or not the list item is missing, so if this file is passed onto someone else, the checking column is doing something very obvious – something that will help you look like a Spreadsheets Pro – so let’s replace our first formula with:
=IF(ISERROR(VLOOKUP(A2,$D$2:$D$9,1,FALSE)),”Missing from List 2″,”OK”)
And drag it down:
What this formula is saying is: “If there is an error in the VLOOKUP formula, then return ‘Missing from List 2’, otherwise return ‘OK’.”
The subtle difference between using IFERROR() and IF(ISERROR()) is the fact that we don’t actually need the result of the VLOOKUP formula to be returned in the checking column, only the fact that there was an error.
Personally, I often would replace the “OK” part of the formula with just a pair of double quotes “” so that there is a blank cell if there are no errors, and only the error message appears when there is an error.