EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
When dealing with large datasets, being able to retrieve the right information quickly and accurately is crucial. Searching manually for data is time-consuming (not to mention boring) and leaves so much room for error.
So how do we avoid having to do this?
One powerful tool that can help you achieve this is the combination of INDEX and MATCH functions in Microsoft Excel.
INDEX/MATCH is among the most popular tools Excel has to offer for performing more advanced lookups in your sheets. It’s made up of two incredibly flexible functions (unsurprisingly called INDEX and MATCH) that allow you to search your sheet using horizontal and vertical lookups, case-sensitive lookups, using lookups based on multiple criteria – the list goes on!
In this article, I’ll show you how to master your data retrieval using INDEX/MATCH, empowering you to become a more proficient and effective data analyst.
Before we dive into the intricacies of INDEX/MATCH, let me explain the basics for those who have never used this ACE function before.
INDEX and MATCH are Excel functions that work seamlessly together to find specific data points within a table.
What’s the difference between the two?
MATCH searches for a specified value in a range and returns the relative position of that item, while INDEX returns the value of a cell in a specific row and column of a range.
One of the significant advantages of using INDEX/MATCH is its ability to dynamically retrieve data.
Unlike VLOOKUP (which I talked about in an earlier article, here), INDEX/MATCH isn’t limited to searching for values from left to right. Instead, it allows you to search for values in any column, offering greater flexibility and control over your data retrieval process.
Now that you have a better understanding of what INDEX/MATCH does, let’s take a look at how to use it.
Let’s break down how to implement INDEX/MATCH effectively. I’ve recorded a short video with instructions on how to perform a lookup using INDEX/MATCH below:
When you have a range of data, you can easily look up and return a value using the INDEX formula. The basic structure of the INDEX formula looks like this:
=INDEX(array, row_num, [column_num])
This will return the specific value of the cell at the point where the row and column numbers you’ve inserted intersect.
The problem we have here though is often when it comes to looking up data in your sheets, you don’t have any clue as to where the specific piece of information sits (i.e. the Row or Column to use).
That’s where MATCH comes in handy! The MATCH formula will return a row number or column number (can you see where I’m going with this?)
The basic structure of the MATCH formula looks like this:
=MATCH(lookup_value, lookup_array, [match_type])
Unlike a VLOOKUP where your match type only consists of two options (0 and 1 (or TRUE and FALSE)), with MATCH you have three options – 1 (Less than), 0 (Exact match), -1 (Greater than).
BONUS TIP: If you’re looking for text rather than a numeric value, I would recommend going with 0 for an Exact Match.
The MATCH formula will return the row or column number where you’ll be able to find the information you’re looking for.
We now know how to find a row/column number, and we know how to search for a specific value – so how do we marry the two together?
We’re going to place the MATCH formula inside our INDEX formula!
All you have to do is copy and paste the MATCH formula you’ve created for your row and paste it inside your INDEX formula at the point where you would add row_num. Then, do the same with your column formula, pasting it at the point you would add your column_num.
You now have an INDEX/MATCH/MATCH formula (I don’t know why they don’t call it that), that will look up a specific value in a data range by searching your rows and your columns.
Isn’t that ACE?!
BONUS TIP: It’s optional to use both the row AND the column. You might want to use one or the other depending on what it is you’re trying to identify in your data.
So, you now know what INDEX/MATCH is and how to use it, the only blank for me to fill in is the why.
Why should you be using INDEX/MATCH in your sheets to help with your data analysis?
Here are a few of the things that make this function so ACE…
Unlike VLOOKUP, which restricts you to searching for values in the leftmost column of your table, INDEX/MATCH allows you to search for values in any column.
This means you can choose the most relevant or appropriate column for your search criteria.
For example, if you have a table with names in one column, dates in another, and corresponding data in earlier (or later) columns, you can search for a specific date and retrieve data from the corresponding column, enhancing your ability to analyse data with diverse structures.
One of the most significant advantages of INDEX/MATCH is that it isn’t affected by column rearrangements.
In Excel, if you use VLOOKUP and later move the desired lookup column, your formula will break, leading to errors in your data.
INDEX/MATCH doesn’t rely on specific column positions. Instead, it uses the column index number obtained from the MATCH function, ensuring your data retrieval remains accurate even if the table’s structure changes.
This stability is vital for maintaining the integrity of your analyses, especially when working with evolving datasets.
INDEX/MATCH significantly boosts performance, especially when dealing with large datasets.
Unlike VLOOKUP, which searches through an entire column, INDEX/MATCH only searches within the specified range, making it much faster and more efficient. With large datasets, this speed difference becomes noticeable and can save you valuable time.
One of the most powerful features of INDEX/MATCH is its ability to handle multiple criteria searches with ease.
By nesting multiple MATCH functions within the INDEX function, you can create complex search conditions. This capability allows you to filter and extract data based on various criteria simultaneously.
For instance, you can search for a specific product in a specific month and retrieve the corresponding sales figure, all within a single formula. This versatility allows you to perform intricate data analyses, providing deeper insights into your datasets and enhancing the precision of your results.
Mastering your data retrieval with INDEX/MATCH can significantly enhance your efficiency when analysing data.
By understanding the fundamentals and leveraging the flexibility of these combined functions, you can streamline your workflow, save time, and ensure accurate results in your data analysis tasks.
So, embrace the power of INDEX/MATCH and take your data analysis skills to new heights!
EXCELACE ALL RIGHTS RESERVED.
WEBSITE BY FARM FACTORy
