Tridge Part Two: Generating an Invoice in Microsoft Excel based on VLOOKUP

In the first of this series, I mentioned the goal of designing a process for a local supplier and keeping a system that invoices the correct produce at the right price across an expected Market Economy.

Tridge is a platform for buyers and sellers of fresh produce across vast regions using a transactional basis for the real-time pricing of goods that fluctuate according to geotechnical reasons.

In this article, we move closer to the closing step of aggregating the important pricing into an invoice that would be easier to submit especially at a point of sale with an order of fulfillment.

We are jumping a few steps from how to use the data available on Tridge, but following along you will notice how I refer to Microsoft Excel for most of my data challenges and can that logic to other similar data software tools.

There are other articles like this, and I would like to mention that the link below also uses VLOOKUP function.

On two separate sheets to price my chili powder as per my unique price, and its organic original story especially for restaurants using these local flavors to boost their menu.

First, in Office 365 the templates available in the menu will work, I have a template visible below with the file name "SimpleInvoice1".

Don't worry about the currency for today, but the purpose of this exercise is to generate the invoice as conveniently as possible. The next article will focus on the connecting of real-time data, and having this part available in your folder makes the whole project work together in its cascading parts.

Why would I use the above screenshots together?

Okay...the "SimpleInvoice" template is a great workbook for practice because the Columns that require the user to populate are C9. As mentioned above, the chili powder is priced according to how I would package the goods. For example, I supply restaurants with Ziploc bags, that I would try and fill with two or three bunches of leafy greens, which I priced according to what the market might price per weight. Rosemary would be cut into sprigs, and edible flowers might be priced per head, so the invoice is custom according to the gardener, but put into an understandable format that makes sense for the chef at the restaurant.

So that will solve the first argument, what VLOOKUP then requires is the range in which it vertically locates its match in Column C, and the range can be vast. The second argument where I entered manual values for the purpose of this exercise, was the "Prices!$A$1$:B$10". For those that need an Excel refresher, the "!" is used because we are asking the function to refer to another sheet and not within its own array. "$" is used in Excel, since as we add more descriptions at C10, after this initial value, the dollar sign fixes the area that we need, otherwise we might move the intended area by one at a time, which might lead to references that are invalid.

The third argument is used in VLOOKUP to tell how many columns to its right, the index needs consideration. Based on the template above, it's convenient that we have built functions like the quantity, and the way it summarizes the price on the total columns. I use the "2" in the third argument, so it will retrieve the column that matches the column of the description. This is effectively the "Column index" based on the VLOOKUP target, dependent on whether my haste to generate the invoice might include spelling errors, the fourth argument, refers to whether the description needs to be an exact match or an approximate match that provides the corresponding price for which this invoice needs, if not, then an error will occur.

I will go ahead and amend the VLOOKUP function, since now I only supply chili powder, and when I decide on dried chilies as another option and product offering, I will change the fourth argument to "1", an approximate value that matches the description as a string, and let's see if it pulls the correct price...

Well...it didn't work, it needs to be a "0" for the fourth argument, as Basil and Arugula were mistaken for the first available price, so this is looking better, with regards to the exact match for Chili Powder.

That is a wrap for the second article, in the next tutorial we will update the prices sheet so that we can refresh the international or the domestic prices for the above-mentioned goods. I will leave the Excel spreadsheet with all the above screenshots in a downloadable link (See the Wordpress link below, look for a simple invoice download). Enjoy practicing!