Enhance your analysis by combining data sets from different topics into a single query tab or dashboard visualization. Insights makes this process super easy with the XLOOKUP functionality. This guide will walk you through what XLOOKUP is and how to use it to merge data from multiple tabs within the same workbook.
❓ What Is XLOOKUP?
XLOOKUP allows you to perform cross-tab analysis by searching for a value in a specified range in another query tab and returning the corresponding value from another specified range. It's a powerful way to combine related data from different sources within your workbook.
Syntax:
XLOOKUP(lookup_value, <query_tab_name>!<lookup_range>, <query_tab_name>!<return_range>)
Example:
You have a query in the Forecasts & Budgets topic showing OTB vs. Forecast data by Stay Month, and want to add the corresponding Pick-up activity from the Pick-up topic. Using Stay Month as the common field:
XLOOKUP(A1, 'Pick-up'!A:A, 'Pick-up'!B:B)
This formula searches for the Stay Month value in A1 within column A of the Pick-up tab and returns the corresponding value from column B of the same tab.
🛠️ How to Use XLOOKUP
Step 1: Prepare Your Data
Identify the Common Field: Determine the common field (e.g., Stay Month) that exists in both tabs. This field will serve as the link between the data sets.
Ensure Matching Data Types: Make sure the common field is formatted the same way in both tabs (e.g., 2024-01).
Step 2: Use XLOOKUP to Combine Data
Add a New Column:
In your main query tab, add a new column where you want to bring in data from another tab.
For example, add a column titled Pick-up New Bookings.
Enter the XLOOKUP Formula:
In the first cell under the new column, enter the XLOOKUP formula.
Reference your common field and specify the lookup and return ranges from the other tab.
Example Formula:
=XLOOKUP(A1, 'Pick-up'!A:A, 'Pick-up'!B:B)
A1refers to the Stay Month in the current row.'Pick-up'!A:Ais the lookup range in the Pick-up tab.'Pick-up'!B:Bis the return range (e.g., Pick-up New Bookings) in the same tab.
Repeat for Additional Data:
To bring in more fields (e.g., Pick-up Cancellations), add another column and adjust the return range in your XLOOKUP formula.
Example:
=XLOOKUP(A1, 'Pick-up'!A:A, 'Pick-up'!C:C)
Here,
'Pick-up'!C:Crefers to the next Pick-up metric column (e.g., Pick-up Cancellations).
💡 Tips and Best Practices
📚 To simplify the process, you can use the Copy XLookup Reference option:
Click on the column header menu of the field you want to reference.
Select Copy XLookup Reference.
Paste this reference into your XLOOKUP formula.
Common Use Cases:
Time Series Joins: Combine time-based data across different tabs.
Fact Lookups: Bring in specific details or metrics from another data set.
Ensure Data Consistency:
Check that your common fields match exactly in both tabs to avoid errors.
🎥 Watch the Tutorial Videos
For a step-by-step demonstration, watch the tutorial video below:
Referencing a single column:
Referencing multiple columns:
🤝 Need Assistance?
If you have any questions or need further help, our Advisory Team is available via chat to assist you.