Hi evoteam21,

Refer below sample code and modify as per your requirement.

Here i am making use of ClosedXml to read the Excel file to DataTable. You can refer the below article for more details.

Read and import Excel file data to DataGridView using ClosedXml using C# and VB.Net

The Excel files

Namespaces

C#

using System.Data;
using ClosedXML.Excel;

VB.Net

Imports System.Data
Imports ClosedXML.Excel

Code

C#

protected void btnSearch_Click(object sender, EventArgs e)
{
    string file1 = @"C:\Users\Admin\Desktop\File1.xlsx";
    string file2 = @"C:\Users\Admin\Desktop\File2.xlsx";
    DataTable dt1 = ReadExcelToDataTable(file1);
    DataTable dt2 = ReadExcelToDataTable(file2);

    var partNos = (from data in dt1.AsEnumerable()
                    where data["TwistWire"].ToString().ToLower().Contains("connector")
                    select data["PartNo"].ToString()).Distinct().ToArray();

    DataTable dt = new DataTable();
    dt.Columns.Add("Partnumber");
    dt.Columns.Add("Status");
    foreach (DataRow dr in dt2.Rows)
    {
        if (Array.IndexOf(partNos, dr["PartNo"].ToString()) >= 0)
        {
            dt.Rows.Add(dr["PartNo"].ToString(), "Exists");
        }
        else
        {
            dt.Rows.Add(dr["PartNo"].ToString(), "missing number %s");
        }
    }

    dataGridView1.DataSource = dt;
}

private DataTable ReadExcelToDataTable(string filePath)
{
    DataTable dt = new DataTable();
    using (XLWorkbook workBook = new XLWorkbook(filePath))
    {
        IXLWorksheet workSheet = workBook.Worksheet(1);
        bool firstRow = true;
        foreach (IXLRow row in workSheet.Rows())
        {
            if (firstRow)
            {
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Columns.Add(cell.Value.ToString());
                }
                firstRow = false;
            }
            else
            {
                dt.Rows.Add();
                int i = 0;
                foreach (IXLCell cell in row.Cells())
                {
                    dt.Rows[dt.Rows.Count - 1][i] = cell.Value.ToString();
                    i++;
                }
            }
        }
    }

    return dt;
}

VB.Net

Protected Sub btnSearch_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
    Dim file1 As String = "C:\Users\Admin\Desktop\File1.xlsx"
    Dim file2 As String = "C:\Users\Admin\Desktop\File2.xlsx"
    Dim dt1 As DataTable = ReadExcelToDataTable(file1)
    Dim dt2 As DataTable = ReadExcelToDataTable(file2)
    Dim partNos = (From data In dt1.AsEnumerable()
                    Where data("TwistWire").ToString().ToLower().Contains("connector")
                    Select data("PartNo")).Distinct().ToArray()
    Dim dt As DataTable = New DataTable()
    dt.Columns.Add("Partnumber")
    dt.Columns.Add("Status")
    For Each dr As DataRow In dt2.Rows
        If Array.IndexOf(partNos, dr("PartNo").ToString()) >= 0 Then
            dt.Rows.Add(dr("PartNo").ToString(), "Exists")
        Else
            dt.Rows.Add(dr("PartNo").ToString(), "missing number %s")
        End If
    Next

    dataGridView1.DataSource = dt
End Sub

Private Function ReadExcelToDataTable(ByVal filePath As String) As DataTable
    Dim dt As DataTable = New DataTable()
    Using workBook As XLWorkbook = New XLWorkbook(filePath)
        Dim workSheet As IXLWorksheet = workBook.Worksheet(1)
        Dim firstRow As Boolean = True
        For Each row As IXLRow In workSheet.Rows()
            If firstRow Then
                For Each cell As IXLCell In row.Cells()
                    dt.Columns.Add(cell.Value.ToString())
                Next
                firstRow = False
            Else
                dt.Rows.Add()
                Dim i As Integer = 0
                For Each cell As IXLCell In row.Cells()
                    dt.Rows(dt.Rows.Count - 1)(i) = cell.Value.ToString()
                    i += 1
                Next
            End If
        Next
    End Using

    Return dt
End Function

Screenshot