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
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
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:
LOOKUPto match sorted data in one range with data in the same position in another range (Documentation: Google Sheets, Excel)VLOOKUPto match data in one column with data on the same row in another column, whether sorted or not (Documentation: Google Sheets, Excel)HLOOKUPto 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 cellAdd 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:B8for 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, enter2for the second column—or3for 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 typeFALSEif 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).
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?
Zapier’s Lookup Table tool is the best option. It’s like a mini spreadsheet designed specifically for LOOKUP. Here's 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.
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 Formatter by Zapier
Want to automate more of your work with text? Check out more ways to use Formatter.
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
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.
