Relational-join Excel tables

You described a linear search algorithm which scales with O(N), where N is more than 400,000 rows.

how I can optimize ... to get the matching results like in my code way faster?

Use an appropriate tool for the task at hand. Put those rows in an RDBMS already. Then you can exploit a column index which takes you right to the proper rows, with O(log N) cost. You can run a DB report which feeds "color this cell Yellow" instructions into Excel.

If you insist on keeping those rows in a spreadsheet, at least sort on the column of interest, and then binary search on it.

The problem is that the time complexity of your code is O(N²). This is bad to deal with 400k rows the better solution, as @J_H suggests, is to put your data in a RDBMS and use SQL to query your data.

If you want to do it by code, first build lookup dictionaries and then use these dictionaries to quickly look up values:

// Define column indices (assuming 1-based index)
int armadasCol = 2; // Replace with actual column index for ARMADAS
int armadawCol = 1; // Replace with actual column index for ARMADAW
int armadaiWCCol = 5; // Replace with actual column index for ARMADAIWC
int sapiWCCol = 4; // Replace with actual column index for SAPIWC
int gCol = 7; // Replace with actual column index for G
int sapsCol = 8; // Replace with actual column index for SAPS

using StreamWriter writer = new StreamWriter(logFilePath) ;

Dictionary<string, (string Armadaw, int Row)> Armadas_Armadaw_mapping = new();
Dictionary<string,(string Sapiwc, int Row)> ARMADAIWC_SAPIWC_mapping = new();
Dictionary<string, (string Saps,int Row)> SAPW_SAPS_mapping = new();
// Read the content of the worksheet and populate dictionaries.
for (int row = 2; row <= worksheet.Dimension.End.Row; row++) // Assuming first row is header
{
    string armadasValue = worksheet.Cells[row, armadasCol].Text;
    string armadawValue = worksheet.Cells[row, armadawCol].Text;

    Armadas_Armadaw_mapping[armadasValue] = (armadawValue,row);
    // ARMADAIWC and SAPIWC
    string sapiWCValue = worksheet.Cells[row, sapiWCCol].Text;
    string armadaiWCValue = worksheet.Cells[row, armadaiWCCol].Text;
    
    ARMADAIWC_SAPIWC_mapping[armadaiWCValue] = (sapiWCValue,row);
     

    // SAPS
    string sapsValue = worksheet.Cells[row, sapsCol].Text;
    string sapwValue = worksheet.Cells[row, gCol].Text;

    SAPW_SAPS_mapping[sapwValue] = (sapsValue, row); 
}

// search for matching 
foreach(var Armadas_Armadaw in Armadas_Armadaw_mapping)
{
    if (string.IsNullOrWhiteSpace(Armadas_Armadaw.Key) || string.IsNullOrWhiteSpace(Armadas_Armadaw.Value.Armadaw))
        continue;

    if (!ARMADAIWC_SAPIWC_mapping.TryGetValue(Armadas_Armadaw.Value.Armadaw,out var SAPIWC))
        continue;
     

    if (!SAPW_SAPS_mapping.TryGetValue(SAPIWC.Sapiwc, out var SAPS))
        continue;

    // Apply coloring to the matching cells
    worksheet.Cells[Armadas_Armadaw.Value.Row, armadasCol].Style.Fill.PatternType = ExcelFillStyle.Solid;
    worksheet.Cells[Armadas_Armadaw.Value.Row, armadasCol].Style.Fill.BackgroundColor.SetColor(Color.Yellow);

    worksheet.Cells[SAPS.Row, sapsCol].Style.Fill.PatternType = ExcelFillStyle.Solid;
    worksheet.Cells[SAPS.Row, sapsCol].Style.Fill.BackgroundColor.SetColor(Color.Yellow);

    string message = $"Match found at row {Armadas_Armadaw.Value.Row}: ARMADA WAREHOUSE = {Armadas_Armadaw.Value.Armadaw}, ARMADA SERIAL = {Armadas_Armadaw.Key}, SAP WAREHOUSE = {SAPIWC.Sapiwc}, SAP SERIAL = {SAPS.Saps}{Environment.NewLine}";
    Console.WriteLine(message);
    writer.WriteLine(message);

}
 
// Save the changes
package.Save();

Note: I didn't run the above code and I don't completely sure if it will work or not, but I just show you the idea.

Others have said that you need to move the data into a "real" database. Given that you're inside a spreadsheet, that's not actually true, since you can use the facilities of the spreadsheet itself to achieve the same effect, albeit with not quite as much speed-up.

First, if the data sources are not sorted, you make two new sheets within the document, to contain the sorted ranges you want to join. (Use an array SORT function to achieve this.) What you do next depends on whether there will be multiple results per key.

In the simple singular case, you can simply add more columns to one of the new sheets, with lookups into the other sheet. You can pretty much just read out the results from that sheet.

In the complex case, you'd need your script, but instead of the inner loop iterating over the entire second page, you could instead call the built-in search function, or failing that, write your own binary search. Either way, this will reduce the time complexity from O(N²) to O(N×log(N)).

If one sheet is much larger than the other, add the extra columns to the smaller sheet.

Edit: technically you only need to sort the range that will be the target of the lookups: add the extra columns to one of the original lists, and have the new sheet sort the other list. However a sorted list of results is often more readable, so you might want to do as I originally suggested anyway.