I have two files File1.txt (it is having 6 columns separated by pipe) and File2.txt (it is having 2 columns separated by pipe)

**File1.txt**

```
NEW|abcd|1234|10000000|Hello|New_value|
NEW|abcd|1234|20000000|Hello|New_value|
NEW|xyzq|5678|30000000|myname|New_Value|

```

**File2.txt**

```
10000000|10000001>10000002>10000003>10000004
19000000|10000000>10000001>10000002>10000003>10000004
17000000|10000099>10000000>10000001>10000002>10000003>10000004
20000000|10000001>10000002>10000003>10000004>30000000
29000000|20000000>10000001>10000002>10000003>10000004

```

The goal is for each row in File1.txt, I have to pick up 4th column and have to search that value in File2.txt. If any match found in File2.txt, then I have to pick up all the rows but only the first column from File2.txt.

This may produce more number of records in the target file. The output should look like below (where the last column 123 is coming from a fixed variable)

```
NEW|abcd|1234|10000000|Hello|New_value|123    (this row comes as it matches 1st row & 4th column of File1.txt with 1st row of File2.txt)
NEW|abcd|1234|19000000|Hello|New_value|123    (this row comes as it matches 1st row & 4th column of File1.txt with 2nd row of File2.txt)
NEW|abcd|1234|17000000|Hello|New_value|123    (this row comes as it matches 1st row & 4th column of File1.txt with 3rd row of File2.txt)
NEW|abcd|1234|20000000|Hello|New_value|123    (this row comes as it matches 2nd row & 4th column of File1.txt with 4th row of File2.txt)
NEW|abcd|1234|29000000|Hello|New_value|123    (this row comes as it matches 2nd row & 4th column of File1.txt with 5th row of File2.txt)
NEW|xyzq|5678|20000000|myname|New_Value|123   (this row comes as it matches 3rd row & 4th column of File1.txt with 4th row of File2.txt)

```

I can write a solution like below, and it is giving me correct output as well. but this one **taking 21 minute odd** when both File1.txt and File2.txt are having **approximately 150K rows**. The final target file generated is having over 10 million rows in it.

```
VAL1=123

for ROW in `cat File1.txt`
do
  Fld1=`echo $ROW | cut -d'|' -f'1-3'`
  Fld2=`echo $ROW | cut -d'|' -f4`
  Fld3=`echo $ROW | cut -d'|' -f'5-6'`

  grep -i $Fld2 File2.txt | cut -d'|' -f1  > File3.txt
  sed 's/^/'$Fld1'|/g' File3.txt | sed 's/$/|'${Fld3}'|'${VAL1}'/g' >> Target.txt

done 

```

But my question is can this solution be optimized? Can this be re-written using AWK or any other way to do it faster?