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.
Download Excel Workbook
6 Easy Ways to Cross Reference in Excel to Find Missing Data
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 below methods 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.
- The VLOOKUP function takes B4 as lookup_value, $C$4:$C$16 as table_array, 1 as col_index_num, and 0 as [range_lookup].
- Whenever VLOOKUP yields in #N/A Error, the ISERROR function puts TRUE or FALSE in places where error occurs or not respectively.
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 Interpolate Missing Data in Excel (4 Ways)
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.
- The MATCH function returns 2 as the lookup_values (i.e., B4) found in row 2 within the lookup_array (i.e., $C$4:$C$16).
- After that, the ISNUMBER function returns TRUE or FALSE depending on the MATCH outcomes. Finally, the NOT function turns the Trues into Falses or vice versa.
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 (3 Easy Ways)
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).
- The ISERROR(VLOOKUP(B4,$C$4:$C$16,1,FALSE) portion works as described in Method 1. Here, this portion is used as logical_test to display missing Cell Value (i,e., B4) or Blank Cell (i.e., “”) depending on the test outcomes; True or False respectively.
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 Deal with Missing Data in Excel (6 Suitable Ways)
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 (7 Ways)
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.
=IF(B4=C4,"Existing Data","Missing Data")
- The IF formula compares each B column entry to a C column entry to insert custom text such as “Existing Data” or “Missing Data”. B4=C4 acts as a logical_test and the formula inserts “Existing Data” if the test returns True otherwise “Missing Data”.
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 Filter Missing Data in Excel (4 Easy Methods)
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 (2 Easy Ways)
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.
Do check out our awesome website, Exceldemy, to find interesting articles on Excel.