**Question:** Given two tables as shown below

In excel you would click on cell D2 and enter the following

=vlookup(B2,F:M,C2+2)

Then drag this down the rows of column 'D' to fill in the values.

In [21]:

```
import pandas as pd
```

In [22]:

```
df_employees = pd.read_csv('hr_employee_data.csv')
```

In [24]:

```
df_employees.head()
```

Out[24]:

In [6]:

```
df_tax = pd.read_csv('tax_table.csv')
```

In [7]:

```
df_tax.head()
```

Out[7]:

**First** I defied a tax bracket from the salary column in df_tax (This may not make sense at the moment but hang on I'll explain at the end)

In [8]:

```
df_tax_braket = pd.DataFrame()
df_tax_braket['Salary'] = df_tax['Salary']
```

And assign each bracket a number 0 to n

In [15]:

```
df_tax_braket['Tax_Braket'] = df_tax['Salary'].rank().astype('int')
```

In [44]:

```
df_tax_braket.iloc[::4]
```

Out[44]:

Now we can join this with df_employees using pd.merge_asof on column 'Salary'

In [26]:

```
df_employees = pd.merge_asof(df_employees.sort_values('Salary'),\
df_tax_braket,on='Salary')
```

In [30]:

```
df_employees.iloc[::4]
```

Out[30]:

**Second** I took df_tax and added Tax_Braket as a column and dropped the Salary column.

In [32]:

```
df_tax['Tax_Braket'] = df_tax_braket['Tax_Braket']
```

In [33]:

```
df_tax = df_tax.drop('Salary',axis=1)
```

In [34]:

```
df_tax.head()
```

Out[34]:

**Finally** we can merge df_tax with df_employees using the regular merge. We need to merge on two columns one is going to be Tax_Braket but the other ... well the other is not a column it's a list of columns... Fear not! We can restructure df_tax to make this work.

In [35]:

```
df_tax = df_tax.set_index('Tax_Braket').T.unstack().to_frame().reset_index()
```

In [38]:

```
df_tax.columns = ['Tax_Braket', 'Dep', 'Tax_Rate']
```

In [39]:

```
df_tax['Dep'] = df_tax['Dep'].astype('int')
```

In [43]:

```
df_tax.iloc[::12]
```

Out[43]:

ok now we can merge on 'Tax_Braket' and 'Dep'

In [45]:

```
df_employees = pd.merge(df_employees,df_tax,on=['Tax_Braket', 'Dep'])
```

In [47]:

```
df_employees.iloc[::4]
```

Out[47]:

**A (maybe impossible) more efficient solution:** the first thing that came to mind was to restructure df_tax as it is (before adding Tax_Rate) and merging with merge_asof on the Salary column and exact match merging on the Dep column all in one function call. I dont think it's possible as of v0.22 (please contact me if I'm wrong).

In [ ]:

```
```