• Home

  • Productivity

  • App tips

App tips

3 min read

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

A LOOKUP guide for databases, spreadsheets, and across apps

By Melissa King · September 11, 2023
Hero image with an icon of a spreadsheet, database, or list

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

In a database, you can link records and view all of their data inside one table (Airtable pictured).

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.

What we're discussing here are one-to-one table relationships, the simplest type of database relationships. With MySQL and other advanced database management systems, you can also build one-to-many and many-to-many relationships—great for building advanced database apps, but more than you need to look up individual data items.

The spreadsheet way: LOOKUP functions

VLOOKUP Function in a Google Sheets spreadsheet
LOOKUP functions are the best way to find related data in spreadsheets (Google Sheets pictured)

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.

Example spreadsheet showing product names, prices, quantities, and details

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.

Zapier is a no-code automation tool that lets you connect your apps into automated workflows, so that every person and every business can move forward at growth speed. Learn more about how it works.

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.

Get productivity tips delivered straight to your inbox

We’ll email you 1-3 times per week—and never share your information.


Related articles

Improve your productivity automatically. Use Zapier to get your apps working together.

Sign up
A Zap with the trigger 'When I get a new lead from Facebook,' and the action 'Notify my team in Slack'