Search Excel Spreadsheets Faster: Replace VLOOKUP With INDEX and MATCH
Pinterest Stumbleupon Whatsapp
Advertisement

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 4 Mistakes You Can Avoid when Programming Excel Macros with VBA 4 Mistakes You Can Avoid when Programming Excel Macros with VBA Simple code and macros are the keys to Microsoft Excel superpowers. Even non-programmers can easily add impressive functionality to their spreadsheets with Virtual Basics for Applications (VBA). Just avoid these programming beginner mistakes! Read More 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.

stock checker vlookup

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 Excel Quick Tips: How to Flip Cells & Switch Rows or Columns Excel Quick Tips: How to Flip Cells & Switch Rows or Columns Quickly bring your data into the proper format, so Excel can do its magic. Use these strategies to quickly flip rows, turn columns into rows, flip rows, and save a lot of manual labor. Read More 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 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're better off setting column widths and row heights manually by typing in values rather than dragging with your mouse. Read More 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 Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Mini Excel Tutorial: Use Boolean Logic to Process Complex Data Logical operators IF, NOT, AND, and OR, can help you get from Excel newbie to power user. We explain the basics of each function and demonstrate how you can use them for maximum results. Read More , 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.

stock checker index

As you can see, we’ve specified the range of cells 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 spreadsheet is called a cell: you cannot divide it any further. Yet, here's how to merge and split cells as you will. Read More 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.

stock checker match

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.

stock checker index and match

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.

stock checker working formulae

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 Before Sharing an Excel Spreadsheet, Make Sure You Do These Things First Before Sharing an Excel Spreadsheet, Make Sure You Do These Things First Sharing is a convenient way to collaborate on Excel workbooks, but before you put your Excel files in the hands of others, prepare your worksheets for sharing 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.

Easy Isn’t Always Best

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 introduce challenges. We'll show you how to change the formatting of your cells and separate numbers from text. Read More , 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 8 Tips for How to Learn Excel Quickly 8 Tips for How to Learn Excel Quickly Not as comfortable with Excel as you would like? Start with simple tips for adding formulas and managing data. Follow this guide, and you'll be up to speed in no time. Read More , 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.

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?

Leave a Reply

Your email address will not be published. Required fields are marked *

  1. Squalle
    May 27, 2017 at 7:01 am

    I've been using this for quite some time now. I think I actually learned this BEFORE I learned VLOOKUP. lol

    I haven't noticed any difference in speed as far as small sheet vs large sheet. I've used in on a few really big spreadsheets without issue.

  2. Boustro
    May 20, 2017 at 7:59 am

    Excellent info. It could be interesting also to know by how much it typically speeds things up on big sheets.

  3. G Srinivas
    May 20, 2017 at 2:12 am

    Excellent tip.Perhaps, if you could also explain how to do this if linked data is in another worksheet

  4. Shirley
    May 19, 2017 at 10:40 pm

    Great tips....learn something new everyday. Thank You.

    • Dictionary
      May 20, 2017 at 12:17 pm

      Something new to learn for May 20: everyday is an adjective that means ordinary; every day means every day.

      • Tina Sieber
        May 21, 2017 at 4:34 am

        Good to know we have a dictionary proofreading our comments. :)