How to create purchase history record with VLOOKUP in Excel

Running a company involves conducting many areas, managing expenses and investments so that everything’s running smoothly. An important question when it comes to a company’s expenses is to know how much was spent on purchases and the history of prices and suppliers to have a perspective of the purchase history and what are the best options for the future.

To have an efficient purchase history, you can use Excel or Google Spreadsheet and apply VLOOKUP, connecting different data and allowing for a simplified understanding about your purchases. In this text, we’ll teach you how to do this!

Walkthrough: How to create purchase history record with VLOOKUP in Excel

1º) It’s necessary to have a purchase record. So, divide the spreadsheet into 4 tabs:

1. Suppliers;
   1. Column 1: Supplier (Name).
2. Products;
   1. Column 1: Amount in stock.
3. Product list;
   1. Column 1: Product;
   2. Column 2: Supplier.
4. Purchases.
   1. Column 1: Purchase;
   2. Column 2: Date;
   3. Column 3: Product;
   4. Column 4: Amount;
   5. Column 5: Supplier. 

Fill in the data in each column, except in “Product” and “Supplier” of the “Product List” and “Purchases” spreadsheets.

2º) In the “Product List” spreadsheet, use “Data validation” in the “Supplier” column. To do this, select the entire column and in “Data” click on “Data Validation”. In the pop-up shown, choose “List” and select in “Source” the items of the “Supplier” column of the “Suppliers” spreadsheet.

Do the same in “Product” of the “Product List” and “Purchases” spreadsheets, but in this case, pull the data validation info from the “Product” column of the “Product” spreadsheet.

3º) Since there’s more than one supplier for each product, it’s necessary that the rows of the “Products” spreadsheet are filled out showing the same products with different suppliers, For example:

After that, go to the “Purchases” spreadsheet, create a new column, name it “Filter” and relate it to the “Supplier” column of that same table.

To create it, do the following:

= transpose (FILTER ($ “Column ‘Suppliers’ $ in table $’ List of Products’ $”; $ ‘Column’ Product ‘$ in table $’ List of Products’ $ = “Product Cell” in table “ Purchases ”); 0))

In the “Filter” column, you’ll have a view of each supplier’s products. 

4º) In the “Products” table, create a new column, name it “History” and create a filter:

= transpose (FILTER($ “Purchase’ $ column in the $ ‘Purchases’ $ ”table; $“ Product ’$ column in the $‘ Purchases ’$ table =“ Product Cell ”in the“ Products ”table); 0))

Now, when you record a purchase in the “Purchases” table, it’ll be reflected in the “History” filter in the “Products” table. This will give you ​​which items were bought in each purchase.

If you are interested, there is an extremely easier way to have your company’s purchase history, it’s with jestor. Check this out!