Using vlookup to Match Lists
(And: Getting rid of the #N/A error)

How do we use vlookup to match lists?

Do you remember that this function looks for a value inside a table, and if it doesn't find one it will show the "#N/A" error code?

Well, this video shows you how we can use this to our benefit when checking if two lists are identical, and to spot the missing values.

What we do, is we name one list, and then we put a vlookup function for every value on the second list. For every unmatched item, you will get the "Not Available" error code. This way you can see which item on the second list is missing from the first one.

The error codes that appear are annoying to the eye, so let's see how can we get rid of them!

Getting Rid of the Vlookup #N/A Error Code

When the vlookup function does'nt find the value inside the range specified, it will write the #N/A code, meaning “Not Available”.

To get rid of this error code, you can put the vlookup inside an IF function together with the ISNA function in the following format: In words:
If the vlookup function is N/A, then leave the cell blank, otherwise perform the vlookup function.

(It is recommended that you be familiar with the If Statement before you proceed.)

An Example (taken from the related video):

(Note the double parenthesis after the word “false” in both of its occurrences)

Let’s cut the above function into three parts, and examine them one by one:

First part: In words:
If the function vlookup(D9,oldList,2,false) gives N/A - (this will happen if it didn’t find the value of cell D9 inside oldList),

Second part: In words:
Then leave the cell blank (double quotation marks mean "blank")

Third part: In words:
Otherwise perform the vlookup as usual.