Microsoft Excel - Excel Formula to Check if Data Exists Across Sheets (Missing or Present)

✅ Excel Formula to Check if Data Exists Across Sheets (Missing or Present)

💡 Use Case

In integration or data-tracking scenarios, you often maintain two different sheets — for example,

• one with a master list of interfaces, and
• another with a subset list you want to verify.

You need to automatically check whether each interface in your current sheet exists in the master sheet, and mark it as “Present” or “Missing.”

---

⚙️ Formula Used

=IF(COUNTIF('OIC Interfaces'!F:F, H2) = 0, "Missing", "Present")

---

🧠 Formula Breakdown

Part	Meaning
COUNTIF('OIC Interfaces'!F:F, H2)	Counts how many times the value in cell H2 appears in column F of the sheet named ‘OIC Interfaces’.
= 0	Checks if the count is zero — meaning the value doesn’t exist in that column.
"Missing"	Returned when the value is not found in the target sheet.
"Present"	Returned when the value is found in the target sheet.
IF(...)	Combines the logic to display either “Missing” or “Present.”

---

🪜 Step-by-Step Solution

1. Identify your reference sheet – for example, 'OIC Interfaces' is your master list of integrations.
2. Choose the lookup column – here, column F in 'OIC Interfaces' contains the unique identifiers or interface names.
3. Write the formula in your working sheet – in cell N2, enter the formula to check if H2 exists in the master list.
4. Copy down the formula – Excel will automatically check all rows and mark each as “Present” or “Missing.”

---

🧩 Example

Sheet 1: Working Sheet

H (Interface Name)	Result
ERP_GL_Extract	(Formula result)
EPM_SFTP_Load	(Formula result)

Sheet 2: OIC Interfaces

F (Interface Name)
EPM_SFTP_Load
HCM_Payroll_Extract

Now when you use the formula:

=IF(COUNTIF('OIC Interfaces'!F:F, H2)=0,"Missing","Present")

✅ For ERP_GL_Extract, Excel shows “Missing”
✅ For EPM_SFTP_Load, Excel shows “Present”

---

🏁 Final Takeaway

This simple IF + COUNTIF combination is one of the most practical Excel tricks for:

• Validating data consistency across multiple sheets
• Checking missing records between reports
• Comparing lists during integrations or reconciliations

It’s lightweight, dynamic, and works perfectly for ERP, OIC, or data validation reports.