vlookup with Python

VLOOKUP stands for 'Vertical Lookup'. It is an indispensible function that makes Excel search for a certain value in a 'table array', in order to return a value from a different column in the same row.

Here's a sample of Python script that can do the same thing like Excel. 

import pandas as pd
import numpy as np

workbook = 'data.xlsx'
excelfile = pd.ExcelFile(workbook)
sheets = excelfile.sheet_names

orders = pd.read_excel(workbook, sheet_name = 'Orders')
returns = pd.read_excel(workbook, sheet_name = 'Returns')

df1 = orders.merge(returns, left_on='Order ID', right_on='ID', how='left')

# Export to NEW excel workbook
output = 'output.xlsx'
df2.to_excel(output, sheet_name='Output', index=False)

Below are the 2 links where I learned from. 

Links:

• https://www.youtube.com/watch?v=cRELNmDpaks
• https://www.youtube.com/watch?v=AHS925L8JVk
  

Enhancing your vlookup function

Here's another tip to help enhancing the way how you use your vlookup function by removing the "#N/A" when the unique key isn't available.

We can remedy this by judicious use of Excel’s IF() and ISBLANK() functions. We change our formula from this…

=VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE)

…to this…

=IF(ISBLANK(A11),”",VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE))

Lock the Table_array in vlookup function

A lot of time, we use vlookup function to help us to retrieve information from another table and we will like to lock the Table_array range. By default, Excel doesn't fix the range and keep shifting it when you copy and paste the vlookup function.

Here's the quick fix.

After you create the 1st vlookup function, it will like this:

VLOOKUP(A11,’Product Database’!A2:D7,2,FALSE)

To lock the table range before you copy and paste the function, add the $ to fix the cell reference, as below:

VLOOKUP(A11,’Product Database’!A$2:D$7,2,FALSE)

This fix the row 2 to row 7. Of course, you can also fix the column A to column D at the same time:

VLOOKUP(A11,’Product Database’!$A$2:$D$7,2,FALSE)

Using VLOOKUP in Excel

This is a short description on how to use "vlookup" function in Excel.

Vlookup function is one of the the most powerful Excel tips that I learn. It works like a database tables, where it can retrieve information from another database table by supplying it an unique identifier. For instance, you can use vlookup function to retrieve the product description (from another sheet) by supplying it the product code.

From the menu Formulas, Insert functions, search the function called vlookup. You need to supply 3 mandatory parameters and 1 optional parameter:

• Lookup_value: [ the unique key]
• Table_array: [ the table range ]
• Col_index_num: [ the column of the retrieved information in the table range]
• Range_lookup (optional): [ sorted or unsorted 1st column in the table range ]

Quickly Remove Blank Row in Excel

Here's a handy way to help you to remove any blank row in a long list.

1. Remove any blank column manually.
2. Select the 1st blank cell in the 1st column.
3. Hit F5 (Edit, Goto).
4. Click Special.
5. Select the Blank option and click OK. This will select all the blank row for you.
6. Now, choose Edit, Delete, select the Entire Row option and click OK.