Affiliate Disclosure: By buying the products we recommend, you help keep the lights on at MakeUseOf. Read more.
Still using VLOOKUP? Here’s how INDEX and MATCH can provide a better solution.
Excel spreadsheets are a great way to organize a large amount of information. However, scanning through that data to find individual records can quickly eat up your time. Fortunately, there are ways to speed up the process.
VLOOKUP is many users’ go-to method when it comes to this kind of task. It’s quick, and it’s relatively straightforward, but it’s not as robust as other alternatives. By employing INDEX and MATCH, it’s possible to avoid some silly mistakes that can often arise while using VLOOKUP. Plus, if you’re dealing with a particularly large spreadsheet, you’ll find that your lookup operations execute much quicker.
Here’s a primer on why it’s worth learning how to use INDEX and MATCH rather than just sticking with VLOOKUP.
How to Use VLOOKUP
First, a quick reminder of how VLOOKUP works. Below is a table with stock names, ID numbers, and prices for various items of clothing.
When I enter a Stock ID tag into cell C10, cell C11 updates with the corresponding price. This is because it checks the string in C10 against the cells contained in our specified range, A1:C8. The 3 in our formula tells Excel that we’re looking for a cell in the third column, because we’re looking for the Price. Finally, adding FALSE ensures that our formula is only going to return values that are exactly the same, rather than approximate matches.
This method works fine, but it’s not ideal if you’re planning on making edits to your spreadsheet at a later date. For instance, if we were to add a new column to our spreadsheet, we run the risk of breaking our formula, as the price column would no longer be third from the left.
By using a slightly different method, we can remove the need to manually count how many columns separate the Stock ID and the Price, removing some potential for human error. This process is also much more efficient in terms of processing power, which can speed things up if you’re working with a huge data set.
How to Use INDEX and MATCH
We can avoid annoying mistakes that VLOOKUP might cause by using INDEX and MATCH instead. This is a slightly more complex method, but it’s really not too difficult to grasp when we split the process up into its component parts.
We’re going to use INDEX to specify a particular set of cells, then we’re going to use MATCH to find the correct cell from that group.
How to Use INDEX
Below is a simple implementation of the INDEX function.
As you can see, we’ve specified the range of cells that contains prices for each item, C2:C8. In this case, we know that the cell we’re looking for is in the seventh row down. However, we can replace this piece of information with a MATCH function, which will allow us to look up a particular cell by entering the Stock ID into cell C10.
How to Use MATCH
Here’s how we’re going to use MATCH in our spreadsheet.
The MATCH function lets us specify a string to search for, which in this case is whatever is inside cell C10. We then describe a range of cells to search, and add a 0 on the end to specify that we only want exact matches. This returns the cell position, which populates cell C12, telling us that the Stock ID that matches the string BE99 is in the fourth cell down, i.e. A5.
How to Combine INDEX and MATCH
To get INDEX and MATCH working in unison, we simply need to sub out our row reference from our INDEX formula with our MATCH formula.
As you can see, the construction of our formula really isn’t too intricate. It’s just a matter of nesting the MATCH function inside the INDEX function, and making sure that both elements are attached to the correct cells and ranges. Now that our spreadsheet is set up in this way, we can make changes to the way it’s set up without breaking our formula.
It might take a little longer to implement INDEX and MATCH than it would to use VLOOKUP, but the result is a more flexible document, with less room for error. If your spreadsheet relies on some kind of lookup functionality, you can save yourself some trouble later on by ditching VLOOKUP and learning how to use INDEX and MATCH properly.
Easy Isn’t Always Best
Excel is a very complex piece of software, and delving deeper into functions can often be quite intimidating. There’s something to be said for simple solutions to problems, but often a more complex method can yield major benefits in the long run.
There’s nothing wrong with using VLOOKUP to perform this kind of task. However, INDEX and MATCH reduce the impact of human error, and don’t require extra edits if and when you make structural changes to your spreadsheet. Using them in unison is only a slightly more advanced technique than implementing a VLOOKUP function, but it offers some major advantages.
Excel becomes more powerful the more you learn about it, so it’s always beneficial to learn new approaches and understand new functions. Don’t rest on your laurels! Take the time to expand your knowledge. And you’ll get the hand of the crazy Excel formulas that can do amazing things.
Are you struggling to implement INDEX and MATCH into your spreadsheet? Or do you have a tip on how to get the most out these functions that you want to share? Either way, why not join the conversation in the comments section below?