Get startedGet started for free

Elevating your lookup game!

1. Elevating your lookup game!

Welcome to this course! Let's discover some of Excel's advanced functions.

2. From Excel - to leading a data team!

I am Agata and just like you, I love learning. Would you guess that my career started with Excel? DataCamp helped me master many languages and technologies and progress in my career! I am now leading a data team and I am thrilled to introduce you to a topic I truly love: Advanced functions in Excel. Ready for a ride?

3. The power of LOOKUP functions

One of the most common tasks in Excel is to look up values in a table based on some criteria. You've learned about VLOOKUP and HLOOKUP functions in other DataCamp courses. These function have a significant limitation: they only work when the searched value is to the right or below the lookup value. Good news is there is a solution to that!

4. What is an array?

But before we learn about it, let's understand what arrays are. An array is a set of row or column of values. Looking at any Excel table we distinguish two components: row and column headers and value array.

5. Drum roll... XLOOKUP!

Knowing what array is, we can now learn a new function: XLOOKUP. It beats V and HLOOKUP as it let's us reference a value in any direction. It's been introduced to Excel in 2021, so older Excel version lack this function. XLOOKUP works with three mandatory inputs: a lookup value, a lookup array and the return array. Other three inputs are optional. Let's look at an example. To lookup the Category of Accessories for cell G2, we can't use VLOOKUP as the searched value is to the left of the lookup value. With XLOOKUP we simply reference the two arrays: lookup array, column C, and the return array, column A. Easy! And I have a pro tip for you! If you apply name ranges to Categories and Subcategories, you can use them in the function!

6. 2D lookups? INDEX()

But what if you would like to do two dimensional lookups? For example, if we were to find the value of Sales for Labels in April, we'd need to search the array, the reference column and row. Enter the INDEX function. It returns the value of a cell within a specified array, here in light orange, and based on the row and column numbers provided, here row 2 and column 2. That looks like too much hard coding?

7. 2D lookups? MATCH()

Then MATCH function is for you. It finds out which row and column to reference, based on a lookup value. For example, it looks up in which row of Categories Labels are, here in row 2. The same with April, here presented as a second Month column. Zero at the end of the MATCH function ensures the exact search match.

8. 2D lookups? (INDEX and) MATCH made in heaven

INDEX and MATCH combo helps to glue it all together. Let's replace the hardcoded elements in INDEX function with reference function to Sales array and two MATCH functions. With names ranges, the function looks very neat!

9. Meet our dataset!

Before we continue, meet our dataset! We'll be analyzing commercial results of a large retailer in the US. With rich order and customer info and a wide range of products - there will be lots of insights to discover.

10. Time for practice!

Ready for the challenge? Let's practice.

Create Your Free Account

or

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