Welcome to this Excel Jet Consult blog post. In this tutorial, we are going to learn how to create 3D Cross Tab Search using XLOOKUP function. Let’s get started
Video
Sample Data
In the screenshot below, we’ve got a sales data that contains Total by Account Manager and Month.
XLOOKUP Formula
Our goal is to group all the sheets from Sales 2015 to 2019 and write the XLOOKUP formula in cell A21. Therefore,
- Click on Sales 2015 sheet, hold down the SHIFT Key and click on Sales 2019 sheet
- In cell A21, execute the formula: =XLOOKUP(B19,A4:A17,B4:M17) XLOOKUP(B20,B3:M3,B4:M17)
- Click Enter
In the screenshot below, we can see $12,769 which is the Total for Connor Rulibeth for the month of July
In cell A21 of the Sales 2018 sheet, we have $28,113 which is the Total for Mansoor Ali for the month of June
See you in the next tutorial