Find string then return different row value Python

I’ve been trying to figure out how to search for a string, and once that string is found look at the cell that is 9 rows below it. The below 3 lines I copied out of the code, the entire code is below as well.

First, I read in TFile column B.

Second, I read in Detail2 this is a large file but I only want the string that is inside cell A1. I make Detail2 into a string. The variable window shows Detail2 as a string and shows the string as A118.

Third, Result uses TFile header NAME to find string Detail2 (A118). And, it does find Detail2(A118). So, up to this point everything is working, I just need to figure out how to now look at the cell 9 rows below Detail2 (A118). Is this even possible to do right in result line?

Here’s a picture of TFile. The header is NAME and Detail2 (A118) is seen at the top. Once the script locates Detail2 I need it to locate the value 9 rows below it so that would be AN000.2000.

   TFile=pd.read_excel(TT, usecols="B")

   Detail2=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()

   result=TFile['Name'].str.contains(Detail2)




import openpyxl as xl 
import os
import pandas as pd
import xlsxwriter
import xlrd
from xlsxwriter.utility import xl_rowcol_to_cell



input_dir = 'C:\work\comparison\NNM'
Summary = 'C:\work\comparison\Summary.xlsx'
TT = 'C:\work\comparison\TT.xlsx'
Dwell = 'C:\work\comparison\Dwell.xlsx'
newFile = 'C:\work\comparison\Comparison.xlsx'




files = [file for file in os.listdir(input_dir)
         if os.path.isfile(file) and file.endswith(".xlsx")]

i=0

wb3=xlsxwriter.Workbook(newFile)
ws3=wb3.add_worksheet('Comparison')


for row_num in range(1,30):
    ws3.write_formula(row_num - 1, 5,
                            '=ABS(C%d - E%d)' % (row_num, row_num))
    
for row_num in range(1,30):
    ws3.write_formula(row_num - 1, 10,
                            '=ABS(H%d - J%d)' % (row_num, row_num)) 
    
for row_num in range(1,30):
    ws3.write_formula(row_num - 1, 15,
                            '=ABS(N%d - O%d)' % (row_num, row_num))     
    

ws3.write_row("A1:Q1", ['TNAME','Start Segment','Start Chainage(ft)','Start Segment','Start Chainage(ft)','Start Chainage Difference(ft)','End Segment','End Chainage(ft)','End Segment','End Chainage(ft)','End Chainage Difference(ft)','Stops','Stops','Distance','Distance','Distance Difference (ft)','Comments'])
ws3.set_column(1, 17, 35)
wb3.close()
 
wb3 = xl.load_workbook(newFile) 
ws3 = wb3.worksheets[0]
 
wb2 = xl.load_workbook(Summary) 
ws2 = wb2.worksheets[1]
 
 
for file in files: 
   input_file =  os.path.join(input_dir, file)
   wb1=xl.load_workbook(input_file)
   ws1=wb1.worksheets[0]
    
 
   Stop1 = pd.read_excel(file,usecols = "F",squeeze = True)
   counts1 = Stop1.value_counts()
   z1 = counts1[0]/2
    
   Stop2 = pd.read_excel(Dwell,usecols = "A",squeeze = True)
   Detail=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()
   counts2 = Stop2.str.match(Detail).sum()

 
   TFile=pd.read_excel(TT, usecols="B")

   Detail2=pd.read_excel(input_file, usecols="A",nrows=1,header=None,squeeze=True).str.slice(start=28,stop=-2).to_string(index=False).strip()

   result=TFile['NAME'].str.contains(Detail2)

    
   ws3[f'A{i+2}']=ws1['A1'].value[28:]
   ws3[f'D{i+2}']=ws1['B4'].value
   ws3[f'E{i+2}']=ws1['D4'].value
   ws3[f'I{i+2}']=ws1['B'][-1].value
   ws3[f'J{i+2}']=ws1['D'][-1].value
   ws3[f'O{i+2}']=ws1['E'][-1].value 
   ws3[f'N{i+2}']=ws2[f'I{i+6}'].value   
   ws3[f'M{i+2}']=z1
   ws3[f'L{i+2}']=counts2
   
   i += 1
   
   wb3.save(newFile)