Conditional vlookup in Python 3

You can construct a series mapping and apply this to the first word of Description.

zeros = df['Price'].eq(0)art_price_map = df[~zeros].set_index('ArtNo')['Price']first_word = df['Description'].str.split(n=1).str[0]df['Price (multiplied)'] = df['Price'].mask(zeros, first_word.map(art_price_map))\                                      .fillna(0).astype(int)print(df)  ArtNo     Description  Price  Price (multiplied)0   AAA      Lore Ipsum     10                  101   BBB      Lore Ipsum      9                   92   CCC      Lore Ipsum      8                   83   DDD  AAA Lore Ipsum      0                  104   EEE  BBB Lore Ipsum      0                   95   FFF  CCC Lore Ipsum      0                   86   GGG  ZZZ Lore Ipsum      0                   0

You can do it with pd.merge like so:

 #create new dataframe with ArtNo created from part of the Description df2 = df.copy()[['Description']] df2.columns = ['ArtNo'] df2['ArtNo'] = df2['ArtNo'].str.split(n=1).str[0] #merge price from the first dataframe df2 = pd.merge(df2, df[['ArtNo', 'Price']], how='left', on='ArtNo') #create a new column 'Price (multiplied)' and fill NANs from original 'Price' column df['Price (multiplied)'] = df2['Price'].values df['Price (multiplied)'] = df['Price (multiplied)'].fillna(df['Price']).astype(int)