![index match excel index match excel](https://www.automateexcel.com/excel/wp-content/uploads/2021/02/index-match-match-2d-lookup-Main-Function.png)
One note of caution: the INDIRECT function is a VOLATILE function, which means it will recalculate every time the worksheet recalculates. So the result of this INDIRECT function is SalesData which is the name of the first data column in our SalesData table. What we've done is concatenate "SalesData". So in our formula, the INDIRECT portion is:
#INDEX MATCH EXCEL HOW TO#
In this tutorial, we will show you how to apply the combination of the INDEX and MATCH functions to make horizontal and vertical lookups, two-way lookups, case-sensitive lookups, and the lookups that meet multiple criteria. =INDEX(INDIRECT("SalesData"),MATCH($B$11,SalesData,0))įor those not familiar with the INDIRECT function, it returns the reference specified by a text string. How to use INDEX and MATCH together in Excel When working with Excel tables, you may constantly find situations that you need to look up a value. 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. 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. Now, on the next tab, I have the same data, but in a table named SalesData. 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. 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. You can learn more about these functions on my blog post located here. I'm going to use the traditional INDEX/MATCH function combination. Here I have a data range with salespeople and sales numbers for 12 products:īelow 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:
#INDEX MATCH EXCEL DOWNLOAD#
If you get a preview, look for the download arrow in the upper right hand corner. You can download the file here and follow along.
#INDEX MATCH EXCEL PLUS#
It’s like we tell Excel: “Look for an employee with last name Jones who is born 07-23-1991 and tell me that person’s salary.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. We, therefore, went ahead and created a unique identifier ourselves by using different criteria, in order to create something unique to look for. In this case, the database didn’t contain any sort of unique identifier or information. “But why even bother combining information in the database?”ĪNY look up function needs a unique piece of information to work. So combining last name and date of birth is the smarter choice as this creates a unique identifier in most cases. In Office 365 we can use XLOOKUP and FILTER to perform fuzzy or partial matches, but in older versions of Excel we have to use VLOOKUP, MATCH and INDEX.
![index match excel index match excel](https://www.exceldemy.com/wp-content/uploads/2021/07/index-match-max-dataset-overview.png)
There are two David Jones’ in our data, but only one employee with last name Jones and birthdate 07-23-1991. There are two scenarios where you might need to use a partial match: When the lookup value is partial When the lookup table contains partial strings. However, if we combine “Last name” and “Date of birth” we would get Jones 07-23-1991.
![index match excel index match excel](https://cdn.corporatefinanceinstitute.com/assets/index-match-formula.jpg)
If we combine “First name” and “Last name” we might end up with a David Jones.
![index match excel index match excel](https://www.excelmaven.com/wp-content/uploads/2018/11/IndexMatch.png)
However combining last name AND date of birth greatly increases the chances of finding a unique value. Unfortunately, none of the information in the employee database is unique, not even last name or date of birth. You might recall that I told you that any lookup function needs to look for a unique piece of information. After this, I mark where I want my criteria and where the formula goes by writing “Last name” in I2, “Date of birth” in I3 and “Salary” in i4.Īdditionally, I hold the left mouse button down on the line between the column names and drag to make them wider, so that the entire content of the cell fits. INDEX MATCH is a clever way to perform a two-way lookup in Excel by combining the power of the INDEX and MATCH functions.