How to Find Records Automatically in Google Sheets, Excel, and Zapier

Spreadsheets excel at crunching numbers. Add a column of figures—your sales or expenses, perhaps—and a quick formula =SUM(A1:A10) can add them up or =AVERAGE(A1:A10) returns the average price. They’re flexible yet powerful.

Databases are better at connecting enormous amounts of data across different tables. You could have a list of your product details in one table, a list of your orders in another, and have each order linked to the products people purchased. Then, instead of typing the price in each time, you’d link the product name in the order table to the product details table and let the database pull in the data on its own. That interconnectivity makes databases more confusing—but also more powerful if you have time to tame the beast.

What if you could get the best of both worlds, a way to look up data when you need it in the simplicity of a spreadsheet—or even something easier? That’s what Excel's VLOOKUP and Zapier’s Formatter Lookup Tables offer. Here’s how to use them.

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 and Microsoft Access make things a bit easier.

In Airtable, say, 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

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. And they can also search through your data and find related values—just in a slightly different way than databases.

All you need are LOOKUP functions, and most spreadsheet apps including Excel and Google Sheets come with three:

• LOOKUP to match sorted data in one range with data in the same position in another range (Documentation: Google Sheets, Excel)
• VLOOKUP to match data in one column with data on the same row in another column, whether sorted or not (Documentation: Google Sheets, Excel)
• HLOOKUP to match data in one row with data on the same column in another row, whether sorted or not (Documentation: Google Sheets, Excel)

The LOOKUP function is more flexible if your data is sorted—but the VLOOKUP or HLOOKUP functions are more likely to get the results you need since they specify exactly how you want to search your spreadsheet.

Let’s try it out. 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. That lets you search column A for a product name, and return a price from column B. Here’s how to set it up:

• Type =VLOOKUP( into an empty cell
• Add your search key—the item you want to search for. typically here you’ll click on a cell in your spreadsheet with the text you want to search for like D7. Or, you could type in some specific text in quotes. Then add a comma.
• Add your search range—the area of your spreadsheet that contains the data to search for and the matching data to return. You could click and select the range, or could type in a range—A2:B8 for this example. Want to make sure the range stays the same even if you move or copy the formula? Add $ before each number and letter, e.g. $A$2:$B$8. Then add a comma as before.
• Add the column number that includes the results. Your spreadsheet will always search the first column to find the item you’re looking for—that’s column 1. If your results are in the column right beside it, enter 2 for the second column—or 3 for the next column, and so on. Add a comma at the end again.
• Finally, if your data is sorted, type TRUE; otherwise (and this is the best option for most spreadsheets), leave the last option blank or type FALSE if it’s unsorted.
• Add the closing round bracket ) to finish the function.

For our example table, here’s the finished function: =VLOOKUP(E13,PRODUCT!$A$2:$B$8,2,FALSE). That will find the product name that you typed in cell E13 and return its price. It’s a handy way to find the price from your product table—or to find any other related data you need in your spreadsheets.

Want to find data from another sheet in the same spreadsheet? Include the name of the sheet and an exclamation mark before the data range—for example if our product data above was in a Product sheet, our function would look like this: =VLOOKUP(E13,PRODUCT!$A$2:$B$8,2,FALSE).

The Zapier Way: Automatically Find and Match Related Data Across Apps

Zapier's Lookup Tables are a great way to match usernames, account numbers, and more

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?

Say you have a customer support form that lets customers pick the product they’re having trouble with—but your inventory app lists the product with an ID number instead of its real name. Or perhaps you want to automatically assign tasks to specific team members, but one app uses their real name and another uses a shorter username. How can you match the two?

Zapier’s Lookup Table tool is the best option. It’s like a mini spreadsheet designed specifically for LOOKUP. Here’s how to use it—with the GIF example above looking up Slack usernames for real names from a form:

• Start your Zapier workflow with the trigger app that has the data you want to lookup.
• Add a Formatter action, select the Utilities option, then choose the Lookup Table transform.
• Click the + icon beside the Lookup Key field and select the text you want to lookup from your trigger app
• Underneath that you’ll see two boxes under the Lookup Table heading. In the box on the left, type in the text that will come in from the first app (a name in our example above).
• In the box on the right, type in the value you want to use in the next app (a Slack username in our example).
• Click the + icon under that to add a new row, then enter a new lookup item and its replacement text. Repeat that until you’ve added all of your options.
• Finally, add a Fallback Value if you want to have Zapier use an alternative if no match is found on your table, then add your next steps to your Zap to finish building the workflow.

Say you want to look up Slack usernames. You’ll add real names in the first column on the left, and the Slack usernames on the right. Then, when the trigger app includes the name Matthew, say, Zapier’s Lookup Table will check for the text on the right column beside that name and return @matt in our example. That’d work to find many things: part numbers, product prices, day or month abbreviations, country codes, email addresses, time zones, and more. Put the item you want to search for on the left, its equivalent on the right, and Zapier will do the rest. You can even use it creatively, perhaps entering work shifts beside team member names to schedule tasks for the right time based on names, say.

Want more details? Check out our in-depth video to get the most out of our Formatter Lookup Tables:

 

---

Whether you’re working with multiple apps or have all of your data in one place, LOOKUP functions are a great way to get database-like features in almost any app. They help you link data in spreadsheets and even make apps connect better in Zapier even when their data doesn’t exactly match.

Do More With Zapier Formatter

Want Zapier to do more with text automatically? Check out our other tutorials for more ideas on how to use Formatter—and how to do those same things in spreadsheet and word processor apps:

• How to Split Names, Addresses, and Other Text
• How to Extract Email Addresses, Phone Numbers, and Links From Text
• How to Automatically Capitalize Text the Way You Want
• How to Find and Replace Any Text in Your Documents
• How to Change Date and Time Formats in Your Text Automatically
• How to Automatically Convert Markdown to HTML

For more spreadsheet tips, check out our free The Ultimate Guide to Google Sheets for detailed guides on how to do more in Google Sheets—and its tutorials work in almost every other spreadsheet app, too.

source https://zapier.com/blog/lookup-data-excel-google-sheets/