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)
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
To follow along go to 3_2_2