**The Complete Guide to INDEX & MATCH**

** CHAPTER 2:**

# Lookup multiple criteria in rows or columns

Some reports involve the need to find a value from a source table using multiple criteria in rows and columns. In this example, we have a table containing both the actual and budget revenues and profits for each application as shown below:

From this data, you need to create a report that returns the value corresponding to three criteria that the user selects:

- Actual or Budget
- Revenue or Profit
- App name

This becomes the matrix lookup, however, unlike the regular cases, this has more than one header (Row 20: Actual or Budget, Row 21: Revenue or Profit).

## Index Match approach

When using the Index Match approach, the first thing you identify is the map or the area that contains the answer. Since this report needs to display the corresponding number from the criteria, the array used is C22:F31. The function at this point is written as:

Cell I22 = INDEX(C22:F31…

Had it required to display a value other than the numbers, you will need to include the cells containing those values as well. For example, if you also need to display the Division, the array becomes A22:F31 instead of C22:F31, since A21:A31 contains the Division values.

For the next argument in the INDEX() function, you need to determine how many rows you need to go down. The MATCH() function is used to find at what row number the lookup value is found. Since we want to use the selected App in cell H22 and matching it with cells B22:B31 which contains the Apps, the function now becomes:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31…

There are three options for the match type argument:

- 1 = less than
- 0 = exact match
- -1 = greater than

Since we want an exact match, we use 0:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0)

Find the column that corresponds to both the criteria selected in cells I20 and I21. Another MATCH() function can be used here. Unlike the regular case, your lookup value is derived from two cells. Combine these criteria using the & symbol.

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21…

The lookup array for this lookup value is found at C20:F21, which has two rows instead of one.

Initially, you would think about using C20:F21 as your lookup array and write the formula as:

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F21,0))

However, instead of displaying the value corresponding to the criteria, it results to an error. This is because MATCH() can only handle a single row or column and cannot handle a combination of rows and columns, such as C20:F21 because it will not know in which direction to move. Had it been a single row such as C20:F20 or C21:F21, it would have worked out fine.

There are multiple ways to resolve this. One way to do it is to revise the table by separating it into two—one containing only the Actual values, and the other containing only the Budget values, and then doing a VLOOKUP to change the source table array depending on the selected criteria. However, it is also possible to resolve this problem without changing the format of the current table. There are three ways to do it.

## Method 1: Using helper cells

Since the MATCH() function can only handle single rows and columns, the simplest way to resolve the problem we had earlier is to use helper cells that combine the values in rows 20 and 21 into one row instead of two. This combination becomes the unique identifier of each column.

Cell C19 = C20&C21

You will see that C19 now contains ActualRevenue. Drag this formula to the right until cell F19.

Go back to the last MATCH() function that used cells I20 and I21 as the lookup value. Instead of using C20:F21 as the lookup array, you can now use the new helper cells C19:F19.

Cell I22 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C19:F19,0))

This formula displays the value that corresponds to the selected criteria in cells I20, I21 and H22.

## Method 2: Using CTRL + SHIFT + ENTER (CSE)

There is another approach that eliminates the use of helper cells. This involves generating an array for the MATCH() function by pressing the keys CTRL + SHIFT + ENTER (CSE).

The MATCH() function is not meant to handle array functions, rather, it looks at things one cell at a time instead of holding things in memory and handle them. Some functions that can handle arrays are:

- INDEX()
- SUMPRODUCT()
- VLOOKUP()
- HLOOKUP()
- AGGREGATE()

However, clicking on CSE on a MATCH() function enables it to handle arrays. Write the same function as above, only instead of using the helper cells, revise the last MATCH() function to combine cells C20:F20 and C21:F21 using the & symbol.

Cell I26 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))

After pressing **ENTER**, you will notice that it results to an error because there is no instruction telling the MATCH() function to keep the values in memory. To see the step-by-step calculation of the function resulting to the error:

- Click on cell I26.
- Go to the
**FORMULAS** - Click on
**EVALUATE FORMULA**.

You will see the function written on the white space. Each click on the **EVALUATE** button will show you the calculation step-by-step. It first finds the value of cell H22, and finds the row containing that App, followed by finding the values in cells I20 and I21 and combining them. You will notice that the value returned by C20:F20 and C21:F21 are #VALUE! The function notices that you are trying to combine things and it is confused because it does not know how to handle such instances.

Click on **CLOSE** to exit the window. Go back to cell I26. Click on the function in the formula bar and instead of pressing the **ENTER** key, click **CTRL + SHIFT + ENTER**.

Notice that it places { } at the start and end of the function and becomes:

Cell I26 {= INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,C20:F20&C21:F21,0))}

It now displays the value that corresponds to the three criteria selected.

Click on cell I26 again and go to **EVALUATE FORMULA** to see how this differs from the previous one. Instead of returning #VALUE! for both C20:F20 and C21:F21, it is now able to find the values in those cells and combine them. The { } gives an instruction to the MATCH() function to keep the values in memory, which makes it easier for it to take C20 and combine it with C21 to give ActualRevenue, D20 with D21 to give ActualProfit, and so on.

It then finds the column number that corresponds to the criteria, “BudgetRevenue”, which is 3. The INDEX() function is now able to find the value using the numbers from the row and column arguments.

This approach is a simple way of writing but it can be confusing for a lot of people. It is only suitable when you are writing the formulas for your own use. Otherwise, if you have other users, it is very likely that those users do not know array functions. They might see the array, click on the formula, inspect it a bit, and press **ENTER**. Since they press **ENTER** instead of **CSE**, the formula will then result to an error again. In such cases, it would be best to avoid this approach.

## Method 3: Using two INDEX() functions

The third approach does not require helper cells nor CSE, but replaces the last MATCH() function with an INDIRECT() function.

Start off using the same functions for the most part except for the last MATCH() function:

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21…

In order to avoid the use of CSE, we need to use a function that can handle arrays. The INDEX() function can be used by putting the entire lookup area inside the INDEX() function. To do this, replace the last MATCH() function in Method 2 with:

INDEX(C20&F20&C21:F21,…

It now becomes:

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,INDEX(C20:F20&C21:F21…

It is important to comply with the syntax of the INDEX() function. The row_num argument is mandatory. Instead of leaving the formula up until C21:F21, you need to specify a row number. In this case, you want to tell the function to take every single row. There are two ways to do that:

- Use the Excel separator (,) and leave it empty

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,INDEX(C20:F20&C21:F21,)…

- Use 0 as the row number

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,INDEX(C20:F20&C21:F21,0)…

Next, comply with the syntax of the MATCH() function where you need to specify the match type. Again, use 0 for an exact match. The final formula becomes:

Cell I29 = INDEX(C22:F31,MATCH(H22,B22:B31,0),MATCH(I20&I21,INDEX(C20:F20&C21:F21,0),0))

This gives you the same result as the first two methods. There are other ways to resolve this problem but I find these three to be the simplest ways.

## Video and Workbook

Feel free to Download the Workbook HERE.

Here’s Part 1 of this guide on INDEX & MATCH.

**Excel Dashboards that Inform & Impress**

Neil R SanchezDecember 17, 2017 at 7:13 amHi Leila, I like your videos a lot, I developed a macro using Index Match for Radiology in Stanford Healthcare…

I currently involved in a big project and I want to know if it is possible to open one instance of Excel if the workbooks belongs to my macro, but if is NOT then open a different instance…. in another words it is like I have two independent Excels:

One for my macro (it allows only the files that I open using my macro), and the…

Second Excel only for the workbooks I try manually open that do not belongs to my macro.

Any comment I will really appreciate… Sorry but this one is the only way I found to ask you this concern.

KamaruddinJanuary 27, 2018 at 4:47 pmVery sweet n excellent teaching of excel..

Boubacar dialloFebruary 1, 2018 at 10:18 pmtremendous! Mrs Leila Gharani

I really enjoyed your video.

Robert WardMarch 8, 2018 at 7:48 pmQuestion. In the example using two criteria, one budget and the second revenue I would like to get a result if one of the results is left empty. I am looking for a dual AND OR condition where if I leave budget or revenue empty I get a result on one of those matches, but also to perform as it does in your example when both revenue and budget have values. But if you empty revenue or budge the result fails. Is this possible with a composite concept like this?

Stephen GuyeMarch 20, 2018 at 7:13 pmI have a lookup that I Have been trying for several weeks now, trying index,match,if,and without success.

Have I asked to much from Excel?

I need to have 4 possible peramiters.

1. Serial number to a certain value (number) (i.e. 1202841)

2. Model number (text) (i.e. BPME7BA-VM)

3. Frequency Value (number) ( = to or > a given value) ( In 1 of 6 columns) (i.e. >=88.1 =90.3 =95.3 =98.3 =101.3 =104.7 <=107.9),

4. Loss value (number) (i.e. -45.74)

Return value (4) to helper cell. This helper cell will then auto populate another worksheet.

ShokJuly 13, 2018 at 12:51 amHi Stephen, I have the same issue. Did you manage to find solution? Please let me know.

SamuelMarch 28, 2018 at 10:29 amYour style of presentation is extremely good. Easy to grasp and attractive, too. Keep it up, ma’am!

Leila GharaniMarch 29, 2018 at 9:47 amThank you! Glad you like the tutorials :)

Sonya Ann MeinertJune 11, 2018 at 1:51 amVery helpful and easy to understand thank you thank you thank you

Leila GharaniJune 11, 2018 at 12:30 pmYou’re very welcome Sonya. Glad you find it helpful.

ikefreshJune 11, 2018 at 3:37 pmI love your teachings, you made it so so simple

Leila GharaniJune 12, 2018 at 7:15 amThank you! I’m glad to hear that :)

MarcoJune 11, 2018 at 8:37 pmI have the following formula (two index functions with indirect functions) – two header lookup. I tested the formula within a single sheet without indirect function and the formula works. However, when in a template and I reference a separate sheet via the Indirect function, the last Index results in a #REF.

Without Indirect Function

=INDEX(CV203:DG258,1,MATCH(CV3&CV4,INDEX(CV3:DG3&CV4:DG4,0),0)))

With Indirect Function

=INDEX(INDIRECT(“‘”&$A9&”‘!”&$D$94&””),B$4,MATCH(B$5&B$6,INDEX(INDIRECT(“‘”&$A9&”‘!”&$C$94&$B$94&””,0),0))), but my last index results in #REF.

In advance, thank you for your feedback.

SalionJune 30, 2018 at 8:08 amHi Leila,

I am trying to use index & match but with 3 criteria in 3 columns and only 1 criteria in the first row (table header). In the top row I have these: Year, City, Job Area, Turnover,Training Expense, Cost per Hire. Then I have 3 different years, 4 cities, and 5 job areas for each city. The turnover, training expense and cost per hire are all values that I need to lookup with an index function or another useful function.

The idea is to bring the values in a prep table with a dropdown, and then create a dynamic graph. I have followed your videos of how to do this, and can do this if there is just one row and one column… but I have 3 columns and I can’t figure out how to do it.

DeeSeptember 4, 2018 at 1:59 amthank you for posting

Question: instead of a single value from the table, what is the formula to provide a dynamic list from this table, example the output is all the productivity apps and their budget profits in order of highest to lowest???

thanks in advance

DeeSeptember 4, 2018 at 2:04 amthank you for posting

Question: instead of a single value from the table, what is the formula to provide a dynamic list from this table, example the output is all the productivity apps and their budget profits in order of highest to lowest???

or what about a list of all apps that had an actual profit greater than their budget profit???

thanks in advance

Rosemberg CaicedoSeptember 4, 2018 at 10:11 pmHi Leila, your web page is great.

I have a question, I’m using index and Match as this: (=IFERROR(IF((+INDEX(Table447,MATCH(Table45[[#Headers],[P1]],Table447[#Headers],0),MATCH([@[Parse_01]],Table447[Column117],0)))>0,1,””),””))

but is not giving me the right answer in the outcome, also when I have the header location in the answer table it doesn’t even gives me an answer.

could you give me any suggestion

thanks for your help

Rosemberg CaicedoSeptember 4, 2018 at 10:13 pmsorry, forgot to write that as I change the match criteria(1,0,-1) it gives me different answers or different match answers that are correct, but misses other ones.

thanks again

AdamSeptember 19, 2018 at 9:59 amHi

I just love the way you explain !!!

AMAZING

AshishOctober 18, 2018 at 6:03 pmHi. Your explanations are really easy to understand. I have seen many of your lessons and have done good progress. Although I am working with a spreadsheet where I need to exicute this column wise, does index works with column as well?

Leila GharaniOctober 23, 2018 at 8:44 amHi Ashish, Thank you! Yes Index works for columns as well.

anuNovember 28, 2018 at 4:34 pmHi thanks for this however it doesn’t work with multiple column instead of muliple rows. Are you able to post a revised version for this. The below index match seems to be the only solution for multi columns using {}

=INDEX(C2:G$6,MATCH($B$14&$B$15,$A$2:A6&B2:B6,),MATCH($B$16,$C$1:$G$1,0))

Leila GharaniNovember 29, 2018 at 7:52 amHi – you will need to switch the arguments around, like you’ve done, except also make sure you look for any exact match. For a non-cse version you can use this formula: =INDEX(C2:F$11,MATCH($B$14&$B$15,INDEX($A$2:A11&B2:B11,),0),MATCH($B$16,$C$1:$G$1,0)).

ROBIN HANSCOMBEMarch 6, 2019 at 9:05 amHi there, I have a table of values (output) and fixed values in the x and y which are the width and height sizes, based upon values in the search (width and height), I am trying to get the value from the table that is the intersection between the two values, the main problem that I have is that the search values will never be the same as the fixed values on x and y and I need to search on the line/ column that is the near higher value in both cases. are you able to help with this one at all please? I hope that this makes sense

Bryon SmedleyApril 7, 2019 at 8:11 pmThank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

Microsoft Excel Tech Community

With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

XelPlus Team

JulioMarch 18, 2019 at 3:52 pmYour method 3 is awesome, thanks for it!! It really saves a lot of time and using the CSE which is annoying method, thanks for sharing this!

Scott MetlenApril 28, 2019 at 11:03 pmhere is a puzzle, I have a list of different products that have various numbers of different parts for each product. I have this information in three columns, first the product name, then the part name, then then number for each product. I need to get the numbers into a matrix where the product name is the row identifier and the part is the column identifier. Any good ideas?

Bryon SmedleyApril 30, 2019 at 10:54 pmThank you for your question. To aid in answering your question, the following link to the Microsoft Excel Tech Community would be the best place to pose your question. If you have a sample file to upload, this would greatly aid in developing a course of action.

The Excel Tech Community has some of the finest minds in the industry. No matter your issue, I’m certain someone there can inform you of the best way to reach your solution.

Microsoft Excel Tech Community

With over 25K members and almost 30K posts, your solution is either ready and waiting or has the possibility of being answered more quickly than we may be able.

Thank you for taking the time to write. I hope you find success with this fantastic group of Excel enthusiasts.

The XelPlus Team

Peter SchulzeMay 11, 2019 at 11:05 amThanks Leila for the great content. I think the link to the Download the Workbook on this page referes to the basic index match book not to the Part 2 one.

Leila GharaniMay 16, 2019 at 4:35 amHi Peter – the download includes both versions. The basic first and if you scroll down the complex version too.

C. MorenoMay 20, 2019 at 6:15 amHi, my matrix uses a range for increments in both the column and row arrays. More specifically, rather than my axes increasing by a certain increment, they are based on ranges. So for example, my columns are labeled 660-700, 701-740, etc. and my rows are labeled 48-64, 65-76, etc.

I’d like to use this formula, but am having trouble. Any suggestions?

MattMay 28, 2019 at 3:20 amAbsolute Lifesaver. Thankyou so much

SebJune 7, 2019 at 1:53 pmfile for part 2 is the same as file for part 1 and missing al lthe additional tabs.

Leila GharaniJune 7, 2019 at 3:06 pmYes – Both versions were put in one file – the other tabs are covered inside the “Visually effective Excel Dashboards” course.