XLOOKUP allows you to search for data in both vertical and horizontal directions, making it extremely versatile. It simplifies complex tasks such as searching, matching, and retrieving information. With XLOOKUP, you can escape the limitations of the traditional lookup functions and embrace a more flexible and intuitive approach to data analysis.
Use cases:
Add 'birthday' to the Orders report
Add 'license expiration' to the Orders report
Add 'email' to the Orders report
Add 'gender' to the Orders report, and much more!
First step: Find two reports that have the information you need combined, with one mutual data point.
For example, the Customer Export and the Orders Report both share customer ID. You can use the customer ID as the look up array to combine the reports.
Tip 💡 The Customer Export is the best report to use when examining customer data because it contains data from your entire customer base in one report.
The Customer report, on the other hand, is only for customers who joined within a date range, so that report might result in blank entries when combined with the Orders report.
To get started, let's combine the orders report and customer export to add customer email addresses to the orders report.
Download the orders report for your desired date range:
2. Download the Customer Export.
3. In the orders report, add a new sheet. Copy the Customer Export Data tab entirely and paste it into the new sheet in the orders report.
4. On the data tab in the orders report, insert a new column. For example, I added column J and titled it "email address"
5. In the first cell of the new column, enter =xlookup. The formula is:
=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
The look up value [value to search for] will be the first member ID on the data tab, mine is I3.
The look up array [range to search in] will be the entire member ID column C on the customer export tab (do not include headers)
The return array [range of values to return] will be the entire email column on the customer export report (do not include headers)
The value if not found will return for blank entries - I'll use " "
Then, use match mode 0 (exact match) and search mode 1 (search first to last)
Separate each value by a comma when creating the formula
Use [command + shift + down] to highlight the entire customer ID column for the look up array (best for when there are no blank entries)
Highlight the first cell for the return array, then navigate to the bottom of the column, press shift and click the last cell in the column to highlight all of the cells for return array (Use this method when there are blank entries in an array)
The function will look something like this:
=XLOOKUP(I3,'Customer Export'!C2:C321,'Customer Export'!E2:E321,"",0,1)
Once the formula populates in the data tab, double-click the bottom right hand corner of the cell to autofill for the rest of the entries. Depending on the size of the file, this might take a few seconds.
If you are adding a number value (like the date) and it populates in a less than desirable format, press command + 1 to open the format dialog box. Select the date format you'd like here.
Here's a quick video that will show you this process step by step:
For more information, check out this article or reach out to us in the Meadow Help Chat