In this tutorial we’ll look at the traditional data range lookup using the INDEX and MATCH functions, but add a twist to look up the values in a table, plus a bonus twist using the INDIRECT function.
You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.
Here I have a data range with salespeople and sales numbers for 12 products:
Below that I want to pull the sales numbers for a specific salesperson from a Data Validation dropdown list, and copy it down for each product:
I’m going to use the traditional INDEX/MATCH function combination. You can learn more about these functions on my blog post located here. I’ll define the range for my INDEX portion of my formula with just the sales data from my data range, and use the MATCH function to define the row and column intersection. The formula is:
Notice that I locked all the cell references using the F4 key to make them absolute cell references, except the one reference to the product in column A, so that will change as I copy it down.
Now, on the next tab, I have the same data, but in a table named SalesData. I can construct the same formula as with a data range, but notice that I don’t need to convert the references to the table to absolute, since they are already defined based on the columns of the table. The formula is:
Now, one last twist. Instead of using the MATCH function to define the column, let’s instead pre-define the range for the INDEX function using the INDIRECT function, and utilize the product names in column A to reference the appropriate column from our Table. That formula will be:
For those not familiar with the INDIRECT function, it returns the reference specified by a text string. So in our formula, the INDIRECT portion is:
What we’ve done is concatenate “SalesData[” with the Product name (in this case, cell A12 is “Prod1”), and then concatenated that with a closed bracket “]”. So the result of this INDIRECT function is SalesData[Prod1] which is the name of the first data column in our SalesData table.
One note of caution: the INDIRECT function is a VOLATILE function, which means it will recalculate every time the worksheet recalculates. For small data sets, this is not an issue, but with large data, that can slow your process down, so be careful when using any volatile function.
What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!