0 of 23 lessons complete (0%)
VLOOKUP Exact Match

Find missing items with Excel Exercise

This is a preview lesson
Purchase this course, or sign in if you’re already enrolled, to take this lesson.

We can also use VLOOKUP to find items that are missing from a list. This is very useful when reconciling accounts.

In the file you downloaded look for the sheet name ‘VLOOKUP Missing Items-1’

In this example we have 2 lists which contain similar information. The problem is that each list contains some unique items and we need to know which items are unique in each list.

  • Using VLOOKUP, in column G, create a formula that checks whether the transaction ID in column B, exists in the transaction ID of List 2 (column L)
  • Again using VLOOKUP, in column Q check whether the transaction ID in column L exists in List 1 (column B)

Video Instructions

To follow along go to 3_2_1


Before you watch the solution, if your answers seem incorrect consider the typical errors made:

  • The most common error on the first part is incorrectly starting your table array at column K instead of L. Remember that VLOOKUP requires that the first column in the table must contain the lookup value. If you put a 2 in the column number thinking that Excel will know to look in column 2, you are mistaken. The column number tells Excel which column to bring back. It will always look in column 1 for the match

Video Solution

To follow along go to 3_2_2