lookup with Partial match and table merge -help needed

Question

Could you help with this?

1. Find all NEW VALUES in Table 1
2. Look for the corresponding Designators for NEW and compare to Table 2.
3. If the Designators is found within the MATERIAL field of Table 2 AND the CNT matches Table 1, create a new merged table with FLOW
4. Create Table 3

Table 1
TYPE 1 TYPE2 Designator CNT LOC FLOW VALUE
BOX LETTER EEE 3 FL EEE3FL ALTERNATIVE
Tube LETTER EEE 3 LA EEE3LA NEW
Tube LETTER EEE 5 MI EEE3MI OLD
BOX LETTER BBB 3 TX BBB3TX NEW
Tube LETTER CCC 4 TX CCC4TX ALTERNATIVE
BOX LETTER CCC 3 MI CCC3MI NEW
Tube LETTER DDD 3 LA DDD3LA NEW
BOX LETTER DDD 5 LA DDD5LA OLD
Tube LETTER CCC 5 FL CCC5FL NEW
BOX LETTER AAA 5 FL AAA5FL ALTERNATIVE
Tube LETTER ABC 5 TX ABC5TX NEW
Tube LETTER ABC 5 MI ABC5MI OLD
BOX LETTER EFG 3 MI EFG3MI NEW
Tube LETTER BBB 5 TX BBB5TX ALTERNATIVE
BOX LETTER BBB 5 LA BBB5LA NEW

Table2
MATERIAL CNT
XXX111222AAAR 3
XXX111222AAAT 3
XXX222111BBBT 3
XXX222111BBBR 3
XXX222111BBBT 5
XXX333222CCCR 3
XXX333222CCCT 3
XXX333222DDDQR 5
XXX333222DDDR 5
XXX333222DDDT 3
YYY222222EEET 5
YYY222222EEER 5
YYY222233EEET 3
YYY223334EFGQ 3
ZZZ3336722CCCT 3
ZZZ333222DDDQR 3
ZZZ33345DDDR 4
ZZZ333552ABCT 5
ZZZ289222EFGT 3
ZZZ222672EEER 5

Table 3
MATERIAL CNT FLOW
XXX111222AAAR 3 NA
XXX111222AAAT 3 NA
XXX222111BBBT 3 BBB3TX
XXX222111BBBR 3 BBB3TX
XXX222111BBBT 5 BBB5LA
XXX333222CCCR 3 CCC3MI
XXX333222CCCT 3 CCC3MI
XXX333222DDDQR 5 NA
XXX333222DDDR 5 NA
XXX333222DDDT 3 DDD3LA
YYY222222EEET 5 NA
YYY222222EEER 5 NA
YYY222233EEET 3 EEE3LA
YYY223334EFGQ 3 EFG3MI
ZZZ3336722CCCT 3 CCC3MI
ZZZ333222DDDQR 3 DDD3LA
ZZZ33345DDDR 4 NA
ZZZ333552ABCT 5 ABC5TX
ZZZ289222EFGT 3 EFG3MI
ZZZ222672EEER 5 NA

ค้นหาด้วยการจับคู่บางส่วนและการผสานตาราง - จำเป็นต้องมีความช่วยเหลือ