VLOOKUP Example- Find Missing Items

Please purchase the course before starting the lesson.

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

Example

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)

Solution

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

To see the full course contents click here.

Back to: Beginner Excel Course > Excel Functions