Join Curt Frye for an in-depth discussion in this video Using INDEX and MATCH to look up data without using the left-most column, part of Excel 2013 Power Shortcuts.
Excel is a terrific program for storing and managing large data collections, particularly data lists, and it's especially handy because you can use built-in functions to look up values in your list if you know other values. In this movie, I will show you how to go beyond the limitations of the built-in VLOOKUP function and give you a much more flexible way of looking up values. I will use the Lookup sample workbook, which you can find in the Chapter 08 folder of your Exercise Files archive. This workbook contains a single worksheet, and there's a very simple list, which has product IDs, descriptions, and quantities for a variety of products made by a company.
If I want to do a lookup and find out what the description is for a product with an ID such as WK-15 that's here in cell C9, then I can click in cell C10 and type =VLOOKUP, then the lookup values in cell C9, a comma. The table array is b3:d7. And note that those are just the values. I didn't include the headers at the top, comma, and then the column index number--in other words, the number of the column for which I want a value to be returned.
In this case, I want it to be from column 2, which is Description, so I'll type 2, right parenthesis, and Return. And there I have the Dress shirt, which is correct. For WK-15, the Description is Dress shirt. Well, that's great! Why in the world would I want to use anything else? Well, the problem is that the VLOOKUP function has a couple of significant limitations. The first is that it can be slow for large data collections. If you've got more than a few thousand rows--and these days many of us do--then VLOOKUP can slow down noticeably.
The other limitation--and this is one I think is more important--is that VLOOKUP can only look up in lists based on the values in the leftmost column. In other words, if you didn't know a Product ID, but you knew that it was a description of a dress shirt, there is no way to find the quantity using the VLOOKUP because you can only look up values in the table using the values in this column. However, by using a combination of the INDEX and MATCH functions, you can do lookups using values in other columns than the first.
Now, before I show you how to combine those two functions into a formula, let me give you a brief tour of the worksheet and some things I did to make the formulas easier to understand. Basically, what I did was create named ranges for the ProductID, Description, and Quantity values. And you can see, when I select cells B3 through B7, that in the Name box, you see ProductID, which is the name of the range B3 to B7. And if I click the Name box's down arrow, you can see that I have similar ranges for Description and for Quantity.
With that information in place, this formula and the next one will be much easier to understand. So, I'll click cell C10, press the Delete key, and my new formula will be =index(. And now I need to identify the named range that contains the value I want the formula's result to display. So, in other words, that is the Description named range here in cell C3 to C7. So, I'll type in the name description Desc. So I have Description, and you can see it's highlighted in blue. Type a comma, and now the match function, match( and the lookup value that's in cell C9, same as it was for the VLOOKUP formula, and now I need to identify the column or the named range where I want Excel to look for the value found in cell C9, and that's in the ProductID named range.
So, I'll type in ProductID, and that's identified in purple, same as the ProductID argument in the formula. Now, I'll type a right parenthesis to close the match function's arguments and another right parenthesis to close the index function's arguments. And when I press Enter, I get the proper value of Dress shirt. Now, that's all well and good, but I just duplicated what I did before with the VLOOKUP. I haven't introduced anything new. Well, here is how you introduce something new. Let's say that I want to find the quantity of dress shirts, but I don't know the ID.
In other words, suppose that all I know is the value Dress shirt, which I can find in cell C10. Well, using a combination of INDEX and MATCH, I can find the quantity using that value from a column other than the leftmost column of the table. So, for that, I would type =index. And again, this first argument of the index function is the range from which I want the formula's overall value to be returned, and in this case, that's Quantity. So I'll type in Quantity, comma, then match, left parenthesis, the lookup value is in the cell C10, and it doesn't matter that it's the result of another formula.
It could be a string value, text that you typed in, or the result of a formula. It doesn't matter. Then I will type a comma, and the lookup array. In other words, in which column is Excel looking for the value in cell C10, and that is in Description. D-e-s-c, description. Type two right parentheses to close out the functions, press Enter, and you can see that the formula in cell C11 took the value in C10, which is in the second column of the data list, and looked up a value elsewhere.
I could use the same technique to put in a description and find the product ID. I think you'll find that the find that the INDEX-MATCH combination is an extremely powerful way of manipulating and looking up your data inside of Excel. It's much more flexible than VLOOKUP and you can use it in many more situations.
- Entering a data series using the fill handle
- Creating hyperlinks
- Setting a print area
- Applying a table style
- Creating substitute data sets using scenarios
- Wrapping and shrinking text
- Creating a data entry form
- Removing duplicate values
- Inserting the current date or time
- Generating a list of unique random numbers
- Calculating running totals
- Analyzing a formula for errors
- Summarizing data with charts and PivotTables