As work gets more complicated, and you’re capturing more data from different sources, working across multiple sheets becomes the norm. Yet connecting data across sheets can be a challenge, especially when dealing with large sets of data. Today, we’re excited to announce that we’ve added cross-sheet formulas to Smartsheet — including VLOOKUP, one of our top customer requests.
Cross-sheet formulas fundamentally change the way you can manipulate data in Smartsheet, giving you more flexibility and power to create connections across all of your team’s work and information, regardless of what sheet that data is in.
Instead of repeatedly entering the same data into multiple sheets, or manually searching through large sheets to copy and paste or create cell links, you can enter a formula once at the top of a column and copy it into the column’s other cells.
Additionally, when you add a new row, cells will be autofilled with the appropriate formulas. This is particularly powerful on a sheet being populated by a form, as the lookup formula information will be added to new submissions without the need to constantly monitor the sheet.
What is a Cross-Sheet Formula?
A cross-sheet formula references data in a different sheet to perform calculations or look up information in a specific cell.
Lookup formulas commonly include VLOOKUP, INDEX, and MATCH. VLOOKUP is used to retrieve corresponding data from other sheets. For example, when using VLOOKUP you could use a customer ID to look up the primary account contact’s name and pull that info into a sheet. When used together, INDEX and MATCH offer an additional, more flexible way to look up information across sheets.
Moving forward VLOOKUP is replacing LOOKUP in Smartsheet, but all of your existing formulas created using LOOKUP will continue to work.
The Power of Connected Data
Now that you can connect data across multiple sheets, you can organize that data much more efficiently. With VLOOKUP and INDEX/MATCH in your toolkit, you’ll save time by using sheets as lookup tables to pull relevant information into another sheet as needed, and you’ll also cut down on the mistakes that can arise from copying and pasting or importing and exporting data.
Here’s an example of how an IT manager uses cross-sheet formulas VLOOKUP and COUNTIF to assign and track IT tickets.