How to find records in Google Sheets, Excel, and across apps

I count on a spreadsheet to keep my client work and earnings in check every month, and I bet you have your go-to data source, too. But the more you use a spreadsheet or database, the higher the chances are that it's chock full of data that can become difficult to find. Ctrl + F can only do so much when you have the same category repeated over and over.

If you find yourself in this situation, you have three more powerful options to try:

1. Link records in a database

2. Use a LOOKUP function in your spreadsheet

3. Set up a Zapier automation to search for data across apps

Here, I'll explain how to use each of these tools to find the exact data you need.

The database way: Linked records

Every time you go through checkout at a store, the point of sales software is looking up everything you purchase in a database. The clerk scans the barcode on an item, turns that into a number, looks up that number in the database, and adds its name and price to your receipt. No one needs to enter the same data multiple times—the database takes care of that.

In developer-focused database management systems like MySQL, you'll do that with JOIN commands that link records across tables. Visual database apps like Airtable make things a bit easier.

In Airtable, for example, you'll add a Link to another record field to your table. Then, to link records, select that field and type in the name of an item from the other table—and Airtable will show a preview of that record's details. That's an easy way to pull in one data item from another table. Need to tag authors in your editorial calendar database from your author table or select hotels from a database for a trip plan? Airtable's Link fields are perfect for that.

The spreadsheet way: LOOKUP functions

Spreadsheets aren't as interconnected as databases. They're designed to list data in one place and calculate sums and averages. But they can also search through your data and find related values—just in a slightly different way than databases.

All you need is a LOOKUP function. Most people opt for VLOOKUP, a function that matches data to the range you specify by searching up and down a range of data (sorted by column). In some edge cases, you might use HLOOKUP, which searches across a row, or LOOKUP, which searches throughout a data range without a specified direction.

Since VLOOKUP is by far the most popular option, I'll dig a little more into its mechanics.

Say you have a product table like the one pictured above, with columns for item name, price, quantity, and description. Underneath are rows including each of your items and their details, in the order you added those products to your store.

Want to find an item's price? A VLOOKUP function is your best option. It'll 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 your preferred tool's name to get in-depth instructions on how to create the formula. As you build the formula, you'll specify what data range you want to search and which column it appears in. The app will return the data you want and put it wherever you tell it to.

Automatically find and match related data across apps

When all of your data is in one database or spreadsheet, linked fields or LOOKUP functions are a great way to link things together. But what if your data is split between multiple apps?

A Zapier Lookup Table tool is what you need. It's like a mini spreadsheet designed specifically for LOOKUP across apps. After you choose your trigger app, you'll add a Zapier tool called Formatter as your second step. Formatter then searches for a specific field in your data and returns the result to you. You can then send that data to another app of your choosing. Here's an in-depth guide for how to automatically find and match related data across apps using Zapier.

Do more with Formatter by Zapier

Sometimes databases are your best bet, but LOOKUP functions are a great way to get database-like features in almost any app. And when data is spread across apps, you can use Formatter by Zapier to mimic a LOOKUP function between those tools.

Here are even more ways to use Formatter by Zapier.

• The Zapier Formatter guide: How to automatically format text the way you want

• How to separate first and last names from a single form field

• How to change date and time formats in your text automatically

• Automatically find and match related data across apps

• The Zapier guide to line items

• Automatically extract data from your apps

This article was originally published in May 2018 by Matthew Guay. The most recent update was in September 2023.