How to find missing records using VLOOKUP() function in Microsoft Excel

In Microsoft Excel, I have used many different solutions to compare lists. Sometimes the solution finds duplicates on one or more columns; sometimes the solution focuses on finding unique values. But what if you want to compare two sets of data in Excel to determine if a record is missing one of the tuples?

UNDERSTAND: Microsoft Excel: Become an expert with this online training (TechRepublic Academy)

For example, in Excel, you want to compare a set of incoming orders with a comprehensive list of delivered orders. Fortunately, it’s not as difficult as you think. In this tutorial, I will show you how to use the VLOOKUP() function in Excel to find missing records in a data set when compared to a comprehensive list.

I’m using Microsoft 365 desktop on Windows 10 64-bit systems, but you can use any version and Excel for the web supports the VLOOKUP() function. For your convenience, you can download demo .xlsx and .xls files.

What is the problem we are trying to solve in Excel?

If your company offers ongoing support, you’re probably familiar with the ordering system. When a customer calls and makes a request, you take a blank order, fill in the details, and move on to fulfilling the request. When the employee makes the request, they complete the purchase order and send it back to the system. I will use the term open to describe an active order and closed to describe a completed order. You can think of a closed list as a subset of an open list. With most online systems today, paper orders are a thing of the past, but it provides us with a simple example.

Picture A display two Table objects. PODistributed, on the left, tracks orders distributed to employees – open orders. Seen from the right, the POR is a list of orders and their return dates, meaning work completed – these are closed orders.

Picture A

As such, there is no reconciliation between the two Tables. We needed a way to highlight open buy orders when there is no matching number in the closed list, meaning that buy order and customer request are still ongoing. Even with such a short list, you can still go wrong if you just line it up visually.

Now that you understand what is needed, let’s add a VLOOKUP() function in Excel to display open orders.

UNDERSTAND: 98 Excel tips that every user should master (TechRepublic)

How to use the VLOOKUP() function to compare two data sets in Microsoft Excel

The VLOOKUP() function is the foundation of many Excel applications. Since Tables do not support dynamic array functions, such as the newer XLOOKUP(), this solution uses the older VLOOKUP() function.

The Excel VLOOKUP() function uses the following syntax:

VLOOKUP(lookupvalue, tablearray, colindex, [rangelookup])

The lookupvalue argument specifies the value to match, the tablearray specifies the range to look for to find the lookup value, the colindex determines the value to return when the function finds a match, and the optional rangelookup argument lets you specify an exact match . When using FALSE for this optional argument, the VLOOKUP() function will return #N/A if the lookup value doesn’t match, and that’s what we’re believing.

To continue, enter one of the following functions into D3 in the PO Distributed page, the list is open, and copy it into the remaining cells:

=VLOOKUP([@[PO Number]],'PO Received'!$B$3:$B$13,1,FALSE)

=VLOOKUP('PO Distributed'!B3,'PO Received'!$B$3:$B$13,1,FALSE)

Use the latter if you are not using a Table object. Reference! $B $3: $B $13 in both must be absolute.

In Excel, the Table object automatically expands (Figure BUT). There were some records where the function did not find a match in the list of closed orders with the list of primary orders. These records are still open. Knowing which order is in is important, especially when it has been open for a long time.

Figure BUT

The function is a bit complicated, so let’s see how it evaluates using the record in row 4:

= VLOOKUP(‘PO Delivered’!B3, ‘PO Received’!$B$3:$B$13.1, FALSE)

= VLOOKUP(102, {103; 101; 106; 104; 110; 109; 107; 108; 113; 115; 111}, 1, FALSE)

= VLOOKUP(102, {103; 101; 106; 104; 110; 109; 107; 108; 113; 115; 111}, 102, FALSE)

The array of closed buys does not include the value 102, so the FALSE argument forces the function to return #N/A. If the value 102 is in the array, the function returns 102.

You can easily stop if you know what the #N/A value means; however, we can add a conditional formatting rule based on the result of the VLOOKUP() function in Excel to highlight the records for which the VLOOKUP() function returns this error. To add a conditional formatting rule, follow these steps.

1. Select B3:D19, which are open orders in the POD Delivered Table. Header cells are not included.
2. On the Home tab, click Conditional Formatting, and then choose New Rule from the drop-down menu.
3. In the resulting dialog box, click the Use formula option to specify the cells to format in the top pane.
4. In the bottom pane, type function = ISNA ($D3) and click Format. Column references must be absolute ($D).
5. Click the Fill tab, select the red color, and then click OK once. SIZE Display function and format.
6. Click OK to return to the worksheet. The result is a visual focus on open orders.

SIZE

You can decide to hide the help column, but I advise against doing so. It’s easy to forget about hidden values, which can make dependency errors hard to fix. Since both lists are Tables, the system will update them automatically when you import new records into the two lists.