Excel X jestor: How to create purchase history records with VLOOKUP

Having a history of purchases is something extremely important for companies, so it’s necessary that recording it is done in the best way. In this text, we’ll teach you two ways of doing that. Do you already know which one’s the best?

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!

Walkthrough: How to create purchase history record without VLOOKUP in jestor

1º) Open your jestor, click on the “+” icon on the left menu, choose the option “Table”, name it “Suppliers” and click on “Create”. Add new fields by clicking on the column header with a “+” and following the steps below:

• Change the name of the “name” field to “Supplier”;
• Add a number field and name it “EIN”.

2º) Create a second table, name it “Products”. Add new fields by clicking on the column header with a “+” and following the steps below:

• Keep the name field;
• Add a number field and name it “Stock”.

3º) Create a third table and name it “Products List”. Add new fields by clicking on the column header with a “+” and following the steps below:

•  Add a connect table field, name it “Product” and select the “Products” table to connect
• Add a connect table field, name it “Supplier” and select the “Suppliers” table to connect

4º) In the “Suppliers” and “Products” tables, fill in the columns  “Supplier”, “Product” and “Stock”.

5º) Go back to the “Products List” table and fill in the “Product” and  “Supplier”columns.

You can see that in “Products”, in the connected panel, there will be a list of suppliers that sell the same product. The same will happen in the “Suppliers” table.

6º) Create a new table in the “+” next to “Tabs” and name them “Items Purchased and “Purchases”.

7ª) In “Items Purchased”, add new fields by clicking on the column header with a “+” and following the steps below:

• Add a connect table field, name it “Purchase” and select the “Purchase” table to connect;
• Add a connect table field, name it “Supplier” and select the “Suppliers” table to connect;
• Add a connect table field, name it “Product” and select the “Products” table to connect;
• Add a number field and name it “Amount”.
• Keep the “name” field.

8ª) In the “Purchases” table, add a new field by clicking on the column header with a “+” and following the step below:

• Add a single selection field, name it “Status” and add the items on the list: “New”, “In Progress”, “Done”;
• Keep the “name” field.

Fill in with your purchases.

In jestor, you’ll create a purchase history and have products, suppliers and purchase details related to it. Your data will be connected all the time, so when a new purchase is created, it will already be saved in the purchase history, supplier and product databases.