Are you still using VLOOKUP? Here is how INDEX and MATCH can provide a better solution.
Excel spreadsheets are a great way to organize a large amount of information. However, scanning that data to find individual records can quickly consume your time. Fortunately, there are ways to speed up the process.
VLOOKUP is the go-to method for many users when it comes to this type of task. It's quick and relatively simple, but it's not as robust as other alternatives. By using INDEX and MATCH, it is possible to avoid some silly mistakes. 4 Mistakes You Can Avoid When Programming Excel Macros With VBA. 4 mistakes you can avoid when programming Excel macros with VBA. Even non-programmers can easily add impressive functionality to their spreadsheets with Virtual Basics for Applications (VBA). Avoid These Beginner Programming Mistakes! Read More Also, if you're dealing with a particularly large spreadsheet, you'll find that lookup operations run much faster.
Here's an introduction on why it's worth learning how to use INDEX and MATCH instead of sticking with VLOOKUP.
First, a quick refresher on how VLOOKUP works. Below is a table of stock names, ID numbers, and prices for various items of clothing.
When I enter a stock ID tag in 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, we tell Excel that we're looking for a cell Quick Tips for Excel:How to Flip Cells and Swap Rows or Columns Quick Tips for Excel:How to Flip Cells and Swap Rows or Columns Get your data into the right format quickly, so that Excel can do its magic. Use these strategies to quickly flip rows, convert columns to rows, flip rows, and save a lot of manual work. Read More Finally, adding FALSE ensures that our formula will only return values that are exactly the same, rather than approximate matches.
This method works fine, but it's not ideal if you plan to make edits to your spreadsheet at a later date. For example, if we were to add a new column How to Manually Set Column Width and Row Height in Excel How to Manually Set Column Width and Row Height in Excel If you want to be super precise in Excel, you better set column widths and row heights manually by typing values instead of dragging with the mouse. Read More
By using a slightly different method, we can eliminate the need to manually count how many columns separate the inventory ID and the price, eliminating some chance of 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 large dataset.
We can avoid the annoying bugs that VLOOKUP might cause by using INDEX and MATCH instead. This is a slightly more complex method. Mini Excel Tutorial:Use Boolean Logic to Process Complex Data. Mini Tutorial:Use Boolean Logic to Process Complex Data. . We explain the basics of each feature and demonstrate how you can use them to get the best results. Read More
We're going to use INDEX to specify a particular set of cells, then use MATCH to find the correct cell from that group.
Below is a simple implementation of the INDEX function.
As you can see, we've specified the cell range How to Merge and Split Cells in Microsoft Excel How to Merge and Split Cells in Microsoft Excel There's a reason why the smallest unit in an Excel worksheet is called a cell:you can't divide further. However, here is how to merge and split cells as you like. Read More which contains prices for each item, C2:C8 . In this case, we know that the cell we are 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 search for a particular cell by entering the Stock ID in cell C10.
This is how we will use MATCH in our spreadsheet.
The MATCH function allows us to specify a string to match, which in this case is what is inside cell C10 . Then we describe a range of cells to search, and add a 0 at the end to specify that we only want exact matches. This returns the cell position, which fills cell C12, and tells us that the stock ID that matches the string BE99 is in the fourth cell down, ie A5.
For INDEX and MATCH to work in unison, we simply need to remove our row reference from our INDEX formula with our MATCH formula.
As you can see, the construction of our formula is really not too complex. It's just a matter of nesting the MATCH function inside the INDEX function and making sure that both elements are connected to the correct cells and ranges. Now that our spreadsheet is set up this way, we can make changes to the way it's set up without breaking our formula.
It may take a little longer to implement INDEX and MATCH than to use VLOOKUP, but the result is a more flexible document, with less room for error. Before you share an Excel spreadsheet, make sure you do these things Before you share an Excel spreadsheet Make sure you do the first thing:Sharing stuff is a convenient way to collaborate on Excel workbooks, but before you put your Excel in the hands of others, prepare your worksheets to share with these tips. Read more . 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.
Excel is a very complex piece of software How to extract a number or text from Excel with this function How to extract a number or text from Excel with this function Mixing numbers and text in an Excel spreadsheet can present challenges. We'll show you how to change the format of your cells and numbers separate from the text. Read More There's something to be said for simple solutions to problems, but often a more complex approach can pay off big in the long run.
There is nothing wrong with using VLOOKUP to perform these types of tasks. However, INDEX and MATCH reduce the impact of human error and do not require additional editing 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 does offer some significant advantages.
Excel becomes more powerful the more you learn about it 8 Tips to Learn Excel Quickly 8 Tips to Learn Excel Quickly Not as comfortable with Excel as you'd like? Get started with simple tips for adding formulas and managing data. Follow this guide and you'll be up to speed in no time. Read More Don't rest on your laurels! Take the time to expand your knowledge. And you'll get the hang of crazy Excel formulas that can do amazing things.
Having difficulty implementing INDEX and MATCH in your spreadsheet? Or do you have any tips on how to get the most out of these features that you want to share? Either way, why not join the conversation in the comments section below?
