Using VLOOKUP() to Identify Missing Records in Microsoft Excel

The problem of finding missing records in one data set compared to another can be solved using VLOOKUP() function in Microsoft Excel.

For example, a company's purchase order system may have two lists: distributed purchase orders (open) and received purchase orders (closed). The task is to highlight the open purchase orders that do not have a matching number in the closed list.

To accomplish this, one can use VLOOKUP() function with the following syntax: `VLOOKUP(lookupvalue, tablearray, colindex, [rangelookup])` where `lookupvalue` is the value to match, `tablearray` is the range to look for `lookupvalue`, and `colindex` identifies the returned value when a match is found.

The function returns an error message (#N/A) if no match is found. To highlight these missing records, one can add a conditional formatting rule based on the results of Excel's VLOOKUP() function. This involves using the ISNA() function to return True if the value in column D is the #N/A error value.