Google Spreadsheets: Functions that I love (part 1)

On May 17th, 2022 in Tech

Yesterday I read an article from Wired (Spreadsheets Are Hot—and Cranking Out Complex Code) and it seems that spreadsheets are the new black.

That made think about how much do I use it on my everyday tasks and I noticed that I’m quite a heavy user and a good one. So I decided to write about it and I decided to start talking about my most used and loved ones.

Most of time I use spreadsheets to record activities and some numeric values. I’m not a heavy user on math functions or beautiful and elaborated charts. I’ve been playing with data science, but it’s a different activity mostly done using Plotly, Pandas and Python.

I wrote “Google Spreadsheets” despite “Spreadsheets” because I’m not using Excel any longer. Google Spreadsheets have a wider range of functions than Excel and the cloud allows you to run heavier functions without worring if you’ll brick your Excel file.

The most used one

UNIQUE + MATCH

Since I learned UNIQUE + MATCH my life spreadsheets had changed. UNIQUE + MATCH is much better than VLOOKUP and it let you work with a large ammount of data at once.

Tldr: UNIQUE + MATCH let’s you find a value on another sheet using a value/cell as reference.

E.g. You have a list of fruits and their weights in one sheet. On the other sheet, you have the list of the fruits and their prices. With UNIQUE + MATCH you can create a list with the fruits, weight and price, all at once.

Why UNIQUE + MATCH?

  • UNIQUE + MATCH is flexible. You can do horizontal/vertical lookups, 2-way lookups, left lookups, case-sensitive lookups and even lookups based on multiple criteria.
  • It works on Excel and Google Spreadsheet

INDEX function

Returns the content of a cell, specified by row and column offset.” (from Google)

Usage: “INDEX(reference, [row], [column])” (row and column are optional)

E.g.: “INDEX(A1:C20, 5, 1)” this function will get value on row 5, column 1 of the reference “A1:C20”. It will get the value from A5.

MATCH function

Returns the relative position of an item in a range that matches a specified value.” (from Google)

Usage: “MATCH(search_key, range, [search_type])” (search type is optional but it is usually defined)

E.g.: “MATCH(“Sunday”, A2:A9, 0)” this function will search for “Sunday” from cell A2 to cell A9. If, for e.g., “Sunday” is on cell A4, the function will return 3.

Now let the magic happen

The result is $689.00

When you join this 2 functions together, magic happens.

From this example, INDEX select, from C2:C5, the 4th value/row. That happens because MATCH search for Nissan (B7) from B2:B5 and finds that it is on the 4th row. The “0” after “B2:B5” means that I’m looking for an exact MATCH.

Where can I use INDEX + MATCH?

INDEX + MATCH allows you to use Google Spreadsheets or Excel like a small database. When you are working with a lot of data, it’s usually useful to separate it on multiple sheets. E.g.: you have a sheet listing all your contracts and, on another sheet, a list of all your clients. A client may have more than 1 contract with you and if you keep all the data in one sheet, you’ll have to change multiple fields every time that the client info is updated.

You can also use INDEX + MATCH to filter data from a large table where you are looking for a specific data, without the use of the filter function.

You’ll notice that INDEX + MATCH can be applied in many different cases that you need to check and validate data.

Tags: digital life spreadsheets

Useful tech tutorials that I found this month (April/2022)

On May 3rd, 2022 in Tech

I decided to catalog nice tutorials and tools that I found out. This post is the first of the season. I’m not sure if I’ll do it every month, but I’ll try to keep it constant.

How to Auto Refresh Chrome Tabs Without an Extension

It’s a tutorial from Technipages that suggest creative ways to refresh a Chrome tab without a Chrome Extension. It’s quite useful and even have a bookmarklet sollution.

Injecting custom JS in any website

This one isn’t a tutorial, but a tool. It’s a extension for Google Chrome called Custom JavaScript for Websites 2 it has over 40,000 active users and it syncs your customs JS on every Chrome session that you are running.

It’s quite useful to create “micro extensions” for websites that you work everyday. I have to work with an online system at work on a web browser. I’m using the CJS Extension to inject JS to the page and highlight tasks that have been designated to my username. I’m also using it to alert me when the inbox is updated.

Tags: automations bookmarklet digital life