This week's YouTube video is a potential game-changer for my fellow side hustle peeps. We use an add-in in Google Sheets called Document Studio to create an order form that automates the customer journey from placing an order to getting an invoice with payment instructions. I hope this will be useful to you. This blog is meant to complement my YouTube channel every now and then, so I am handling a popular lookup reference function in Excel/Google sheets called VLOOKUP and the IF formula I use to clean up the order collection form.
Here is this week's video for your guidance and reference
Scenario
We have orders of our special candles coming through via Google Forms and we want to compute the total amount to invoice the customer by looking up the unit price in one sheet and multiplying it by the number of candles in another sheet.
VLOOKUP
Here is the formula we created using VLOOKUP:
=VLOOKUP(F2,Prices!A2:B5,2,)This is how the VLOOKUP function works:
In our example, the VLOOKUP function is entered in cell I2 of Column I; the unit price column.
The search_key is the type of candle in cell F2
The range is our price list in the Prices sheet (Prices!A2:B5) and the Vlookup function searches the first column of the price list (Item)for the search_key
The index, which is the column index where our desired value is located and is got from our range/price list, is 2. Because the Item column in the price list has index 1
Putting it all together, we want the unit price of a blue candle in column F. We tell Google Sheets to look inside the price list table/range, check the column that has the candles and when it finds the blue candle, we want Excel to return its corresponding price in the adjacent column.
The IF formula
The IF function is one of the most useful functions in Excel and I believe everyone should know how it works. The IF function checks for a condition and returns one value if the condition is TRUE and another value if the condition is FALSE.
In our example, we want to remove the NA errors or trail of zeros from the cells using the IF function below.
...if(isblank(F2:F),,VLOOKUP(F2:F,Prices!A2:B5,2,))The NA's and trail of zeros occur because we told Google Sheets to apply the formula to all the cells in the related columns (F2:F). We are trying to take care of our future selves so that we only have to perform the calculation once. However, the errors and zeros show up because there are no entries in the subsequent rows. Every time an order is made, the error and zero will be replaced by meaningful values. We could leave our sheet with the errors in it, but we just do not like mess, so we used the IF function to clean it up.
Our logical_expression/condition is isblank(F2:F). ISBLANK searches the whole F column (F2:F) for empty cells and returns TRUE if a cell is empty and FALSE if a cell is not empty. When we get an order, the corresponding cell in the F column will have a type of candle and therefore will not be empty
Because we want the cells in F2 to remain empty when they do not have entries; instead of returning the errors, we enter nothing in the value_if_true section of the IF function
In the value_if_false section of the IF function, we tell Google Sheets to return the result of our VLOOKUP formula
I also mention the ARRAYFORMULA function in the video, and you can learn more about it in this post.
Conclusion
Today, it is important to watch the YouTube video and follow along, otherwise, this might not be very useful to you. VLOOKUP and IF are some of the most popular functions used in Google Sheets and Excel.
Let me know if you have any questions or comments about this week's blog article and YouTube video. Don't forget to like, subscribe, comment, share, etc.
Have a good week!
Comments