How to Cross Reference in Excel to Find Missing Data (6 Ways)

In day-to-day usage, while doing routine work, users need to find missing data using cross-referencing between lists or worksheets in Excel. Therefore, users using cross reference in Excel to find missing data is pretty common. Users can fetch or indicate certain data as missing using multiple functions within the same or different worksheets.

Let’s say we have two columns that hold student names. Now, we want to find the missing entries among the columns by using cross referencing.

This article demonstrates ways to cross-reference in Excel to find missing data. For cross-referencing, we’ll use multiple functions such as ISERROR, VLOOKUP, NOT, ISNUMBER, MATCH, and IF functions, as well as Conditional Formatting.

How to Cross Reference in Excel to Find Missing Data: 6 Easy Ways

Comparing a column’s data to another or a cell value to others is simply referred to as cross-referencing. Users can use cross reference to find missing data in Excel. However, to apply cross-referencing, users use multiple formulas, constituting multiple functions. Go through the methods below to be able to find missing data using cross-referencing.

Method 1: Applying ISERROR and VLOOKUP Functions to Identify Missing Data

The VLOOKUP function returns the lookup_value, matching it with a table _array. Otherwise, it returns missing data as a #N/A Error. As a result, combining the ISERROR and VLOOKUP functions results in TRUE or FALSE, indicating TRUE as a missing data indicator.

Step 1: Type the following formula into the cell D4.

Step 2: Press ENTER to apply the inserted formula, then drag the Fill Handle to execute the formula for other cells.

Read More: How to Deal with Missing Data in Excel

Method 2: Cross Referencing Data Using NOT, ISNUMBER and MATCH Functions

Similar to Method 1, in this case we use a combined formula using NOT, ISNUMBER and MATCH functions. The MATCH function returns the row number of the lookup_value within the lookup_array. Then the ISNUMBER function converts the matched lookup_value number into TRUE or FALSE. Finally, the NOT function reverses the logic to display TRUE for missing data entries.

Step 1: Paste the following formula into the D4 cell.

Step 2: Use the ENTER key to execute the formula. Also, drag the Fill Handle to apply the formula to other cells to indicate missing data using cross referencing.

Read More: How to Find Missing Values in Excel

Method 3: Fetching Missing Data Using IF, ISERROR and VLOOKUP Functions in Excel

When users want to fetch data that is missing from the list, they can add a condition using the IF function to the ISERROR and VLOOKUP formula. We know the IF function takes a logical_test to execute the provided operation. Here, the ISERROR and VLOOKUP combined formula are used as the logical_test to fetch the missing data.

Step 1: Insert the below formula in any cells (i.e., D4).

Step 2: Hit ENTER to apply the formula. Afterwards, drag the Fill Handle to display other missing entries.

⧭Tips: Select the entire column > Move to the Home tab > Hover to the Font section > Choose any desired Font colors to highlight the fetched data.

Read More: How to Filter Missing Data in Excel

Method 4: Conditional Formatting Unique Values to Highlight Missing Data

Highlighting unique entries can indicate missing data using Conditional Formatting. Conditional Formatting offers rule types – Format only unique or duplicate values to custom format entries.

Step 1: Highlight the columns containing different lists. Then go to Conditional Formatting > New Rule.

Step 2: The New Formatting Rule window appears. In the window,

➤ Select Format only unique or duplicate values as Select a Rule Type.

➤ Choose Unique under Format all.

➤ Click Format > Select a Fill Color.

➤ Finally, click OK.

🔺 Excel highlights the unique values by comparing the adjacent column values as shown in the below image.

Read More: How to Compare Two Excel Sheets to Find Missing Data

Method 5: Using IF Function to Cross Reference and Find Missing Data in Excel

Sometimes, users have identical lists to cross reference missing data from. So, users need to match each entry from one list against another.

Suppose the following image depicts the fruit sales lists for cross reference to find missing data.

Step 1: Type the below formula to compare identical values among lists.

Step 2: Apply the formula using the ENTER key, then use the Fill Handle to display the custom texts. Follow the instructions to apply Conditional Formatting to the outcomes as depicted in the following image.

Read More: How to Find Missing Values in a List in Excel

Method 6: Cross Referencing Data from Different Worksheets to Find Missing Data

What if the fruit sales lists exist on different worksheets? In that case, the combined formula using IF, ISERROR, and VLOOKUP functions can fetch the missing data by cross referencing any one list.

The Fruit Lists may exist in different worksheets, as shown in the latter picture.

Step 1: Adjacent to Fruit List 1 or 2 add a helper column named Missing Data. Insert the following formula in any cells of the column.

Step 2: Click ENTER then drag the Fill Handle to display the missing fruit names as shown in the below image.

Execute the instructions to preformat the cell if you want to display entries in custom formats.

Read More: How to Count Missing Values in Excel

Download Excel Workbook

Conclusion

This article demonstrates the usage of multiple functions to cross reference in Excel to find missing data. However, some methods only indicate the missing data while others not only indicate but also fetch it. Go through the Formula Explanations to comprehend each function’s outcome and its impact on the formula. Comment if you have further inquiries or have anything to add.

Related Articles

• How to Find Missing Rows in Excel
• How to Fill Missing Values in Excel
• How to Remove Missing Values in Excel

<< Go Back To Missing Values in Excel | Data Cleaning in Excel | Learn Excel