I rely on spreadsheets to manage my client work and income every month, and I’m sure you have your own preferred data source as well. However, the more you use a spreadsheet or a database, the harder it becomes to find the data you need, especially when you have the same category repeated over and over again. While Ctrl + F can help to a certain extent, there are three more powerful options to try:
The Database Method: Linked Records
Whenever you make a purchase at a store, the point-of-sale software searches for everything you buy in a database. The cashier scans the barcode of an item, converts it into a number, searches for that number in the database, and adds its name and price to your receipt. No one needs to input the same data multiple times – the database takes care of it.
In developer-focused database systems like MySQL, you can achieve this with JOIN commands that link records between tables. Visual database applications like Airtable make things a bit easier.
In Airtable, for example, you would add a “Link to Another Record” field to your table. Then, to link records, you select that field and enter the name of an item from the other table – Airtable will display a preview of the details of that record. It’s an easy way to pull a piece of data from another table. Need to identify authors in your editorial calendar database from your authors table or select hotels from a database for a travel plan? Airtable’s link fields are perfect for that.
The Spreadsheet Method: VLOOKUP Functions
Spreadsheets aren’t as interconnected as databases. They are designed to list data in one place and perform sum and average calculations. But they can also search for data and find related values, just in a slightly different way than databases.
All you need is a VLOOKUP function. Most people opt for VLOOKUP, a function that matches data to the range you specify by searching top to bottom (based on columns). In some specific cases, you might use HLOOKUP, which searches horizontally across a row, or LOOKUP, which searches across an entire range of data with no specified direction.
Since VLOOKUP is by far the most popular option, I will delve a bit more into how it works.
Let’s say you have a table of products like the one shown above, with columns for the item name, price, quantity, and description. Below are the rows containing each of your items and their details, in the order you added these products to your store.
You want to find the price of an item? A VLOOKUP function is the best option. It will search column A for a product name and return a price from column B. You can use this function in both Google Sheets and Excel – click on the name of your tool of choice for detailed instructions on creating the formula. When building the formula, you specify the range of data you want to search and in which column it appears. The application will return the desired data and place it where you indicate.
Finding and Automatically Linking Related Data Between Applications
When all your data is in a single database or spreadsheet, linked fields or VLOOKUP functions are a great way to connect items together. But what if your data is spread across multiple applications?
A Zapier lookup table is what you need. It’s like a mini spreadsheet specifically designed for searching between applications. After selecting your trigger app, you’ll add a Zapier tool called Formatter as the second step. Formatter then searches for a specific field in your data and returns the result to you. You can then send this data to another app of your choice. Here’s a detailed guide on how to find and automatically link related data between applications using Zapier.
Do More with Formatter by Zapier
Sometimes databases are your best choice, but VLOOKUP functions are a great way to achieve similar database-like functionality in almost any application. And when data is spread across applications, you can use Formatter by Zapier to mimic a VLOOKUP function between those tools.
Here are even more ways to use Formatter by Zapier:
- The Formatter Zapier Guide: How to Automatically Format Text Exactly How You Want It
- How to Split First and Last Names from a Single Form Field
- How to Automatically Change Date and Time Formats in Your Text
- How to Find and Automatically Link Related Data Between Applications
- The Zapier Guide to Line Items
- Automatically Extract Data from Your Apps
This article was originally published in May 2018 by Matthew Guay. The latest update took place in September 2023.
For even more tips and techniques to optimize your spreadsheets and databases, check out Crawlan.com, an essential resource for Google Sheets experts like myself.
