XLOOKUP and INDEX MATCH in action

1. XLOOKUP and INDEX MATCH in action

In the first video of this course we have learned three new functions, XLOOKUP, INDEX and MATCH. It’s now time to see them in practice! Let’s get started by reviewing VLOOKUP and XLOOKUP. In this sheet we’ve got Sales summarized by channel. Below, we have two tables where we will look up various values and categories, using V- and XLOOKUP functions. Let’s start with VLOOKUP. Because the Sales column is to the right from Sales channel column, we can simply perform a VLOOKUP to obtain the Sales. So VLOOKUP, Cell B16 Corner Shop, then the array C5 to D11 in the top table, finally, column 2 and range lookup false. Let’s not forget to lock the ranges pressing F4 while typing the function. That worked well, let’s copy the calculation down the cells. What happens when we want to use VLOOKUP to reference the Ownership column, which is to the left from Sales channel? Perhaps we can use a negative column reference? No, it didn’t work! Let’s try the same one more time, using XLOOKUP function, starting with Sales. We need a lookup value, so Corner shop. Then, we need the lookup array, so Sales channel cells C5 to C11 as well as the return array, so the Sales cells D5 to D11, each time locking the ranges of the array, by pressing F4. Great! Let’s copy the values down the table. How about the Ownership lookup? Let’s apply another XLOOKUP using the same logic. Wonderful! Now that we know XLOOKUP, we can wave good old V- and HLOOKUPs goodbye! Ready for more referencing adventures? Let’s progress to INDEX and MATCH. In the sheet we have a sales evolution over time by Country and a small table below where we would like to lookup specific values per Country and per month. For reference, we highlighted these values in the top table. INDEX and MATCH combination allows us to lookup values in a two-dimensional table. Let’s start by typing INDEX. We now need to reference an array, which is here. We will lock this reference with F4. Next, we need to find the row number, so to find the Country. We type in MATCH, select Belgium and then select the Country headers in the above table, using the absolute references locked with F4. Lastly, we input 0 to ensure the exact column is selected). Finally, we need to reference the Months, so after the comma, we type in Match once again, then we select the requested Month value and reference the Month header in the above table. Once again we press F4 to lock the range, and we type in zero to ensure the exact column is selected). Enter and that’s it! Let’s drag the calculation down. The function looks very long. Let’s apply some name ranges to make it easier to read. We will select the values and call them Sales in the Name box. Let’s validate by pressing Enter. Similarly, we will name the Countries and Months. Let’s now replace the cell references in the function with the name ranges: Sales, Country and Months. Enter. And that’s it. Our function is much more readable! Now, over to you!

2. Let's practice!

Create Your Free Account

By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.