I am looking for an advice how to transform the following excel function into a working python code “=IFERROR(VLOOKUP(AA5;’Sheet3′!B:L;11;FALSE);VLOOKUP(AA5;’Sheet3′!B:L;11;TRUE)”

In my code, the cell AA5 is represented by the value ‘Cell AA’ and Sheet3 is recorded as df_E.

I have checked and both types of data utilised in the vlookup function (‘Cell AA’) & the data recorded in the column B of the sheet 3 are objects.

Appreciate any help in this matter.

import pandas as pd
import numpy as np

df_main = pd.read_excel(r'C:UsersSPycharmProjectsTTest.xlsx', sheet_name='Sheet1')
df_lookup = pd.read_excel(r'C:UsersSPycharmProjectsTTest.xlsx', sheet_name='Sheet2')
df_E = pd.read_excel(r'C:UsersSPycharmProjectsTTest.xlsx', sheet_name='Sheet3')

df_main['Cell AA'] = df_main.apply(lambda row: f"SFEFF_{row['banka'][3:6]}__{row['filter']}x{row['big']}", axis=1)

df_main['Cell AB'] = df_main.apply(lambda row: str(row['BDD']) + row['sit'] if row['BDD'] == 5 else row['BDD'], axis=1)

df_main['Cell AC'] = df_main['Cell AB'].astype(float)

result = pd.merge(df_main, df_lookup[['Support', 'Factor 1', 'Factor 2', 'ratio']], left_on='Cell AB', right_on='Support', how='left')

df_main['Cell AC'] = result['ratio']

df_main['Cell AD'] = df_main['obklad'] * df_main['Cell AC']

def vlookup(df, lookup_value, column_index, exact=True):
    try:
        if exact:
           
            result = df.loc[df.iloc[:, 0] == lookup_value, df.columns[column_index - 1]].values
        else:
            
            result = df[df.iloc[:, 0] <= lookup_value].iloc[-1, column_index - 1]
        
        
        return result[0] if exact and len(result) > 0 else result
    except (KeyError, IndexError):
        return None


def apply_vlookup(row):
    
    exact_result = vlookup(df_E, row['Cell AA'], 11, exact=True)
    if exact_result is not None:
        return exact_result
    else:
        
        return vlookup(df_E, row['Cell AA'], 11, exact=False)


df_main['Cell AE'] = df_main.apply(apply_vlookup, axis=1)


print(df_main)