Simpler analysis

In the “recipe” Simple sales analysis, you could learn how to get to the value that is in the intersection of the row and column of the sales report. On that occasion, we used the INDEX and MATCH functions. The new XLOOKUP function allows this analysis to be performed in a much simpler way, by combining two instances of this function. Let’s see how it’s done!

We start from the report in which we monitor the sales of wine by quarters. First, let’s create two lists. The first will be used to select the desired item (wine). We will use the second one when choosing the quarter for which we want to show the value of sales. In both cases, we will use the Data Validation option.

We will use two XLOOKUP functions to display the value of sales in the intersection of the column and the row. Using the first, we will find the row in which the selected wine is located, and we will nest it in the second, which searches the selected quarter. The formula should look like this:


What if we wanted to emphasize a cell at the intersection of a row and a column to make it more noticeable, that is, to assign it a yellow background? Nothing easier, you need to run the Conditional Formatting option to create formatting rules using a formula, and then enter the expression:


and choose to make the background color of the cell yellow. Each time we select a new item, i.e. a quarter, the value of sales will change and the corresponding cell at the intersection of the type in which the selected item is located, i.e. the column in which the quarter is located, will get a yellow background.