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**.

**Table of Contents**hide

**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**.

`=ISERROR(VLOOKUP(B4,$C$4:$C$16,1,0))`

**Formula Explanation**

**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.

**[Notes.]**In case you want to highlight the

**Trues**in the result, highlight the entire column >

**Conditional Formatting**> Select

**Highlight Cells Rules**> Select

**Equal to**> Select the

**Cell**(i.e.,

**D6**) > Choose appropriate

**Fill Color**to format > Click

**OK**.

**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.

`=NOT(ISNUMBER(MATCH(B4,$C$4:$C$16,0)))`

**Formula Explanation**

**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**).

`=IF(ISERROR(VLOOKUP(B4,$C$4:$C$16,1,FALSE)),B4, "")`

**Formula Explanation**

- 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")`

**Formula Explanation**

**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.

`=IF(ISERROR(VLOOKUP(B4,'List 1'!$B$4:$B$16,1,FALSE)),B4,"")`

**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)**

**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.

Do check out our awesome website,** Exceldemy,** to find interesting articles on Excel.