Functions INDEX & MATCH

This tutorial covers several aspects of the functions INDEX and MATCH. In  Part I you will learn the fundamentals of how combining both functions. In Part II, you will learn advanced techniques such as embedding the function INDEX with a double function MATCH and also how to build a Cross-Tabulated report. Finally, in Part III there`s an extensive explanation on how to use Boolean Logic in the functions INDEX and MATCH to extract datapoints from multiple arrays of your dataset.

Part I (Index & Match – Basics)

Index & Match – Definition:

For the background of this example, we have the table below which shows, on the left side, a list of products for sale in a store and the respective quantity sold of each item last month. On the right side, we have a drop-down list showing all the names of the products. Our goal is to display in cell F3 the exact quantity sold for each item we choose in the drop-down list from cell E3. To achieve that we need to use the combination of the functions INDEX and MATCH.

Visually answering the questions:

The 2 dynamic questions in the image above will help you understand the logic behind the combination of the functions INDEX & MATCH whenever you need identify a look up value. In the specific example of the product “Air Fryer”, you need to answer 2 questions to identify the correct quantity associated with that product:

1. What`s the row position of Air Fryer in the column Product Type? (i.e. 4 as per table provided)

2. Which value can you see in the row position number 4 of the array Quantity? (i.e. 15 as per table provided).

By looking at the table you could manually insert the numbers 4 and 15 in cells F6 and F9, respectively, to answer these questions.

In Excel terms, the first question is answered by the function MATCH. In other words, that function will return the row (or column) position of a specific look up value in an array (i.e. 4 for the product Air Fryer as that product is at the 4^th position of the array). The second question is then answered by the function INDEX. In other words, this function INDEX will return you the value of a cell in a particular intersection of a row and column. In this case there`s only one column to look at, and the row position is basically the output of the function MATCH. Therefore, the “quantity” associated to the 4^th position of that array is 15.

Let’s now check the arguments each function individually.

Functions INDEX & MATCH in isolation.

We can delete the number 4 from cell F6 and get the same number through the function MATCH as per steps below:

1. Firstly, insert that function MATCH in cell F6 and for the argument “lookup value” select the product type “Air Fryer” from cell E3.

2. The argument “look up array” is the range B3 to B19 and for the argument “match type” let`s select 0 as we want the exact match to be returned here.

3. Then, press ENTER and the value “4” will be returned in that cell, which represents the position of “Air Fryer” in the array “Product Type”.

We can also delete the number 15 from cell F9 and get to the same number through the function INDEX as per steps below:

1. Insert the function INDEX in cell F9 and for the first argument “array” we need to select the range C3 to C19.

2. After that, the argument “row number” is basically the row position we are interested in, which in this case is 4.

3. For the argument “column number” we can choose either 1 or 0 because we are looking into a single column. If this range was composed of several columns we would need the exact column position to complete this function. In this case we can basically select 0, close brackets and press ENTER. The result 15 is also returned as expected.

Combining both Functions:

Now we basically need to combine these functions. So, we can select cell F9 and copy the function INDEX after the equals sign and then paste that function in cell F3.

As we initially inserted manually the number 4 as the argument “row number” of the function INDEX, all you need to do now is replace this number 4 by the output of the function MATCH. There are basically 2 ways you can do it. First, you can simply delete the number 4 and create a link to the cell F6 which contains the output 4 of the function MATCH. When you press ENTER the final result 15 is still returned here.

The second method is basically copying the function MATCH (from cell F6) after the equals sign and replace that link you created just so you have a function inside another function. This process in Excel is called “nesting” and it`s  very common among advanced Excel users, so make sure you get used to it. We can then press ENTER and the final result will still be in place.

So, we no longer need the intermediary steps (questions) and for each product type you choose in the drop-down list, you will have the respective quantity sold displayed in this cell F3.

Part II (Index & Match – Advanced)

Quick recap of Index Match Part I:

Let`s first recap the basic use of the INDEX and MATCH functions. Let’s consider the Example 1 below, where we have a table showing products for sale and their respective quantities sold last month.

To extract the quantity sold for the product “Laptop” (cell B14), you should basically repeat the process we just described in Part I and your combined INDEX and MATCH function must return the following:

After you do this, you can choose any variable from the drop-down list and receive the accurate result automatically for each one.

INDEX function with a double MATCH Function:

Now, we will extend our usage of the INDEX function to make it dynamic for columns and rows. In this example, we have a list of product types, the quantity for each, and 2 columns indicating whether the products are imported or not.

Let’s focus on an imported “air fryer” product for the sake of this example and start creating the INDEX function like this:

1. First, insert the INDEX function and use the range F4 to G7 as the argument “array”.

2. As you can see, the air fryer is in the first column of the array, so the argument “row number” must be 1.

3. Now, imported products are positioned in the first column of the range, so the argument “column number” will also be 1.

4. Press ENTER and the result is 18.

Now, we want to make this dynamic with the MATCH function so we can get a different result when we choose different inputs from the drop-down lists.

MATCH Functions:

Follow these steps:

1. Insert the MATCH function in cell E11 for now and the “lookup value” here will be the air fryer, which is 1.

2. After that, for the “lookup array” argument, select the range E4 to E7, which are the product types.

3. Press ENTER and the result is 1, as expected.

4. The next step is to repeat the same process in cell F11 to create another MATCH function for the variable Imported Status instead of the variable air fryer. The result here will also be 1.

Nesting INDEX with 2 MATCH Functions:

Finally, we must nest these functions. Like this:

1. Copy the first MATCH function after the equal sign.

2. Go to the INDEX function and replace the “row number”, which is 1, with the copied MATCH function.

3. Then, do the same thing with the second MATCH function to replace the argument “column number”, which is 1, in the INDEX function.

4. Press ENTER and now your function is flexible for both drop-down lists.

How to display your data in a Cross-Tabulated format

Let’s learn how to organize any dataset in a cross-tabulated or matrix-style report using the INDEX and MATCH functions. In Example 3, we have three columns of data, including product type, imported status, and the quantity of each. These rows represent unique combinations.

However, we can make this look a lot better by organizing the entire dataset in a matrix format. Let’s use the product Air Fryer for this example:

1. So, insert the INDEX function, where the “array” argument must be the entire quantity range from K4 to K11.

2. As you can see, Air Fryer and Imported Status are both in the first position of their arrays. As such, let’s add 1 and 1 as the “row number” and “column number” arguments for now.

3. Finally, press ENTER and the result is 15.

Adapting the Function MATCH to accept 2 Lookup Values:

Now, let’s use the MATCH function to make this more dynamic. The difference here is that we are working with two lookup values instead of one because we need to consider the variables product type and Imported Status simultaneously. That means we must use an ampersand (&) to connect the values within our formula. Follow these steps:

1. Insert the MATCH function and type I14 as the “lookup value”, insert ampersand (&), and type J13, which represents “yes” for the Imported Status.

2. Then, for the “lookup array” argument, select the range from I4 to I11 representing the product types, insert ampersand (&), and select the range from J4 to J11 representing the Imported Status.

3. Finish the function with 0 as the “match type” argument because you need an exact match.

4. Press ENTER and the result is 1, which represents the correct position of this combination of variables.

Nesting the Functons:

Now, we just need to nest the functions so we can get the correct results for the entire table:

1. Copy the MATCH function you just created after the equal sign.

2. Then press F2 to open the INDEX function and replace the “row number” argument with the MATCH function.

3. The argument “column number” can be 0 or 1 because we’re looking at a single column array.

4. Press ENTER and the result is 15, which confirms the combined functions are working well.

The next thing you need to do is expand the function, but first, we need to make sure the correct references are in place. Otherwise, we’ll get errors.

To get the right results, all the “array” arguments of the nested functions need to be in Absolute Reference. So, select the “array” arguments one by one and press F4 once to lock them up.

Now, we can work on the relative references of the combined “lookup value” argument. Go to cell I14 and press F4 three times to lock up only the column. Then, go to cell J13 and press F4 twice to lock up only the row in this reference.

Finally, you can expand the nested function to the entire range and obtain the correct results for all the variables in the table.

Part III (Index & Match – Pro Tips)

All starts with a basic INDEX function

In this example, we have 3 different inputs, which are type of product, type of warranty, and type of sale. Each row represents a unique combination of inputs with a specific price.

For this practice exercise, we will work with “laptop”, “partial warranty”, and “online sale” as our inputs. Our goal is to create a function that can give us the right price for any combination of inputs we choose from the dropdown lists.

Let’s start by creating a basic INDEX function:

1. First, insert the INDEX function on cell E14.

2. Then select the range E3 to E11 for the “array” argument.

3. Type 5 as the “row number” argument, which is the row position of the laptop input in the array.

4. After that, use 0 as the “column number” argument, which can be 0 or 1 because we’re focusing on a single column.

5. Close brackets, press ENTER, and the result will be 415, which is the correct price.

Now, we need to make this function dynamic. This process won’t be different from what we’re already learned in the last 2 INDEX and MATCH explanations (Part I and Part II).

The only change is that, in this case, we need the MATCH function to work with more than one variable at a time because we have multiple arrays. That’s why we need to use Boolean Logic to adapt the function MATCH.

Adapting the function MATCH through Boolean Logic

Boolean Logic is a mathematical system that represents logical expressions and relationships using only two values: TRUE and FALSE. This means Excel can recognize something as TRUE or FALSE and then associate it with numbers.

Let’s test the Boolean Logic before we use it to adapt the MATCH function:

1. Go to cell F3 and type the equal sign (=) followed by B3, another equal sign, followed by B14.

2. Press ENTER and we get the result FALSE.

What we did here was ask Excel if the input B3, which is air fryer, is equal to the input B14, which is laptop. Since the answer is no because these two products are not the same, the result is FALSE.

Now we can use this formula to analyse an entire array of values:

1. So, go back to cell F3 and delete the previous work.

2. Type the equal sign and select the entire product array from B3 to B11.

3. After that, type another equal sign and select cell B14

4. Press ENTER and we’ll have an entire array of TRUE and FALSE results.

5. You can then expand the same formula two cells to the right to obtain the TRUE or FALSE values for the rest of the inputs in the table.

If there is even one FALSE value in a row, it means the entire row is FALSE. Keep that in mind because the only row that serves our purpose is the one that has TRUE values across the board.

Clear Results with Conditional Formatting:

To make this a lot easier to see, let’s use Conditional Formatting:

1. So, click the Conditional Formatting option, go to Highlight Cells Rules, and click on Equal to.

2. Then type the word “false” and then choose the option Light Red with Dark Red Text”.

3. Click OK.

4. Repeat this process one more time but type the word “true” and choose “Light Green with Dark Green Text”.

5. Click OK.

Now we can see the correct result very clearly.

At this point, it’s important to remember TRUE and FALSE are only the starting points because these values can be associated with numbers. This allows Excel to perform mathematical operations. As such, TRUE equals 1 and FALSE equals 0.

For example:

1. Go to cell I3 to multiply the first three rows F3, G3, and H3, which represent FALSE, FALSE, and TRUE.

2. Press ENTER and the result will be 0.

3. Now, expand the multiplication to the entire array to get all the numerical values.

As you can see, the only row where all the valuables are TRUE equals 1 is the row 7. This is how the Boolean Logic works and it will allow the MATCH function to look for the value 1 among an entire array of values instead of just looking at a single value at a time.

Recreating Boolean Logic inside the function MATCH

The next step is to recreate the Boolean Logic in the MATCH function so it can return the number 5, which is the row number where all of our inputs are. Otherwise, the INDEX function won’t work. Follow these steps:

1. Firstly, go to cell E15 and insert the function MATCH.

2. Now, use 1 as the “lookup value” argument.

3. For the “lookup array” argument, start by selecting the entire product type array from B3 to B11.

4. Then, type the equal sign (=) add B14, and close brackets.

5. Now, let’s multiply by the second condition, which is the entire warranty type array, from C3 to C11.

6. Add an equal sign and type C14.

7. After that, let’s multiply again and then select the entire sale type array from D3 to D11.

8. Then, type the equal sign and type D14.

9. Lastly, use 0 as the “match type” argument because you need an exact match.

10. Press ENTER and the result will be 5, as expected.

Embedding the Functions:

The only thing we have to do now is copy this MATCH function after the equal sign and use it to replace the row number (5) in the INDEX function we created at the beginning of this tutorial.

Now there`s only one issue to tackle. If we choose a combination of variables such as “laptop”,”partial” and “store”, there will be no direct corresponding combination in the dataset, and therefore this INDEX & MATCH function will return an error.

Let’s check how we can manage it.

IFERROR function – Final Touch

To create the IFERROR function, follow these steps:

1. Open the INDEX MATCH function from cell E14.

2. Insert the IFERROR function at the beginning of the formula.

3. Go to the end of the format by pressing CTRL and arrow down simultaneously.

4. Add a comma, write “n.a.” (not available) with quotation marks, and close brackets.

5. Press ENTER to apply.

Now, whenever you choose a combination of variables and there’s no available result according to the existing data, you will see the “n.a.” message instead of an error. With this well-structured formula in place you can also get rid of all side calculations performed previously.