Perform a left merge, this will use sku column as the column to join on:

In [26]:df.merge(df1, on='sku', how='left')Out[26]:   sku  loc   flag dept0  122   61   True    b1  122   62   True    b2  122   63  False    b3  123   61   True    b4  123   62  False    b5  113   62   True    a6  301   63   True    c

If sku is in fact your index then do this:

In [28]:df.merge(df1, left_index=True, right_index=True, how='left')Out[28]:     loc   flag deptsku                 113   62   True    a122   61   True    b122   62   True    b122   63  False    b123   61   True    b123   62  False    b301   63   True    c

Another method is to use map, if you set sku as the index on your second df, so in effect it becomes a Series then the code simplifies to this:

In [19]:df['dept']=df.sku.map(df1.dept)dfOut[19]:   sku  loc   flag dept0  122   61   True    b1  123   61   True    b2  113   62   True    a3  122   62   True    b4  123   62  False    b5  122   63  False    b6  301   63   True    c

A more generic application would be to use apply and lambda as follows:

dict1 = {113:'a',         122:'b',         123:'b',         301:'c'}df = pd.DataFrame([['1', 113],                   ['2', 113],                   ['3', 301],                   ['4', 122],                   ['5', 113]], columns=['num', 'num_letter'])

Add as a new dataframe column

 **df['letter'] = df['num_letter'].apply(lambda x: dict1[x])**  num  num_letter letter0   1         113      a1   2         113      a2   3         301      c3   4         122      b4   5         113      a

OR replace the existing ('num_letter') column

 **df['num_letter'] = df['num_letter'].apply(lambda x: dict1[x])**  num num_letter0   1          a1   2          a2   3          c3   4          b4   5          a

VLookup in VBA is just like pandas.dataframe.merge

I always look for so many procedures for VBA in the past and now python dataframe saves me a ton of work, good thing is I don't need write a vlookup method.

pandas.DataFrame.merge

>>> A              >>> B    lkey value         rkey value0   foo  1         0   foo  51   bar  2         1   bar  62   baz  3         2   qux  73   foo  4         3   bar  8>>> A.merge(B, left_on='lkey', right_on='rkey', how='outer')   lkey  value_x  rkey  value_y0  foo   1        foo   51  foo   4        foo   52  bar   2        bar   63  bar   2        bar   84  baz   3        NaN   NaN5  NaN   NaN      qux   7

You can also try the following to do a left merge.

import pandas as pdpd.merge(left, right, left_on = 'key', right_on = 'key', how='left')

outer or left act like SQL, python's built-in class DataFrame has the method merge taking many args, which is very detailed and handy.