Since we started building Omni, we’ve focused on designing a product to help everyone achieve their data goals, regardless of their experience with SQL and other BI tools. That’s why we’re excited to launch support for xlookup(): to give users access to Excel’s trusty syntax to connect data across queries.
If you learned Excel before SQL, lookup functions (vlookup, xlookup, etc.) might’ve been your first taste of the power of joining datasets together. The mental models of an Excel lookup() and a SQL join are actually quite similar — take some values, see where they match values in another table, and return some information from rows where those values match. Even though the syntax is different, the goal is the same.
We’ve spent the past few months bringing the functionality of Excel into Omni (e.g. Calculations, Calculations AI), but the ability to reference fields from other queries was one missing piece we’ve been eager to build. After we hunkered down and built a few tech dependencies, it’s now a reality!
Unlike a SQL join, xlookup()’s magic comes from a lower barrier to entry — you don’t need to know how to write a join or define relationships in your data model to make it work. Even though you might be able to accomplish what you want with other tools, it’s hard to beat the speed of writing a 3-argument function that you are already familiar with.
For example, some tools let you follow a “Merge Results” workflow, where you define a “primary query”, select the fields you want from that query, replicate that process for every additional query, and then run the merge process to return a separate “merged” table. While this works, it requires you to navigate a different UI outside your table and deeply understand the tool to do so. We wanted to make it easier to use, so you don’t need to jump around – instead, all the magic happens right inside your existing query.
Even in spreadsheet-style BI tools where an
xlookup()-style function might exist, the process of using it differs from Excel. Instead of a‘$’, you might have to write a‘/’; instead of adding a column to the right and entering a function, you might have to click a separate button and figure out a new UI. These differences may seem trivial, but they add unnecessary cognitive load.
We’re committed to replicating the Excel workflow to give you the brain space to do great analysis, not fuss over a clunky process or new syntax. And because the calculations you write in Omni are valid Excel functions, when you download a query or dashboard as an XLSX file, the function definitions will come with it!
Using xlookup() in Omni
When you use xlookup(), we’re still running a join behind the scenes – we’re just using Excel syntax to tell Omni what fields to join and what fields to return. Our query engine prunes the queries for only the information it needs, completes the join between the tables, and returns a column filtered to the row that matches your lookup_value.
Even though the technical implementation differs from Excel, our goal is that it feels just as seamless to end users as Excel itself. For example:
- If you change the name of your tab, your tab reference will change with it
- If you insert new columns, your cell references will still point to the original columns
- And, if you want to create a new calculation that references the xlookup() value, you can reference it just like any other Excel calculation in Omni
This cross-tab lookup opens up a ton of new easier workflows, including:
- Doing a lookup on the fly, without formally defining the join relationship: Use
xlookup()to add user data to an orders view. Then, if it’s useful, do a bit of just-in-time data modeling to formally define the join relationship.
- Merging queries on a foreign key: Use
xlookup()to create a monthly summary table by bringing together monthly metrics from different aggregate tables by “joining” on the month. This is easier (and more performant) than using a date spine and defining a SQL join.
- Interactive use cases: Since
xlookup()dynamically references other tabs, you can use free-text entry to pull in ad-hoc adjustments and annotations, giving your data a “live” feeling. For example, suppose you’re building a model to forecast orders. You can create a separate tab for the assumptions you want to use in the model, edit those assumptions freely, and pull in those values usingxlookup()to create your projections.
xlookup() is our next big step toward lowering the floor for end users in BI platforms. You should always be able to interact with data how you’d like to, whether that’s with SQL, Excel calculations, a point-and-click UI, an AI query accelerator, etc. – and needing to pull data from your data warehouse shouldn’t change that. A quick xlookup here, a SQL join there – it’s all part of the flexibility we’re building, and we’re excited about the use cases that this functionality will continue to unlock.
If you’d like to play around with this for yourself, we’d love to show you.