How to match large data on excel based on some criteria?

I am using MS Office Pro Plus 2019. I have an excel that has 6 columns inside.

A = ARMADAW, B = ARMADAS, D = SAPIWC, E = ARMADAIWC, G = SAPW, H = SAPS

I would like to color the matching values B and H. But there are criterias.

• For ARMADAS B2: D542295890, I am getting the ARMADAW A2:B05KAYIP
• I will look for B05KAYIP in the E column ARMADAIWC and get the corresponding D SAPIWC value 0010
• Then I look for 0010 in column G SAPW and get the matching value of D542295890 ARMADAS B2.
• If B and H matches then I want to color them.

I wrote a console application with C# but since there are approx. 395000 rows it takes too much to complete. In fact it is still trying to match the values right now :)

Is there a faster way with Excel to do this? I need help here.

Here is sample data:

ARMADAW ARMADAS     SAPIWC  ARMADAIWC       SAPW    SAPS
B05KAYIP    D542295890      0001    TT Mobil_SITES      34CS    210305601410F7000079
B06KAYIP    99121839        0010    Consumer Str.Loc        0030    210305601410F7000083
B05KAYIP    210212D4726TB1004467        0011    E-SIM Brysl Digi        34CS    210305601410F7000078
B05KAYIP    21013158489TAB002570        0012    E-SIM M2M Digitl        34CS    210305601410F7000077
B06KAYIP    21021205889TAB002094        0030    B08KAYIP        0055    210305601410F7000076
B08KAYIP    CM90012939      0040    Public Stor.Loc.        0055    210305601410F7000075
B05KAYIP    FP110305448     0050    Other groups StL        34CS    210305601410F7000074
B08KAYIP    FP110305447     0055    B06KAYIP        34CS    I0I0728878
B05KAYIP    I0E1717261      0060    Corporate Str.L.        0055    99121839
B011KAYIP   00004240        0065    Scrap Str.L.        34CS    I0I0728685
B05KAYIP    05004326        0080    Buffer Stock        0030    FP110305447
B013KAYIP   05004336        34CS    Macka Stor.Loc.     34CS    I0I0728756
B05KAYIP    05004345        0100    G-ADN       34CS    I0I0728837
Macka Stor.Loc. I0I0728837      0101    ADANA_OMC       34CS    I0I0728797

Here is the error message when I use this formula:

=EĞERHATA(KAYDIR(İNDİS(H$2:H$4,KAÇINCI(B2,H$2:H$4,0)),,-1)=İNDİS(D$2:D$4,KAÇINCI(A2,E$2:E$4,0)),YANLIŞ)

This formula in a cell (e.g. J2 and drag down) looks for the appropriate values in the two tables, and if a match is found return TRUE otherwise FALSE

=IFERROR(OFFSET(INDEX(H$2:H$4,MATCH(B2,H$2:H$4,0)),,-1)=INDEX(D$2:D$4,MATCH(A2,E$2:E$4,0)),FALSE)

The result of the formula on sample data:

This is Markdown format with site https://tabletomarkdown.com/convert-spreadsheet-to-markdown/