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:

  1. Click on cell I26.
  2. Go to the FORMULAS
  3. 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.

Excel Download Practice file

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

Excel Dashboards that Inform & Impress

Use these techniques in your own reports

Unbeatable value!

LEARN MORE

23 Comments

  1. Neil R Sanchez December 17, 2017 at 7:13 am - Reply

    Hi 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.

  2. Kamaruddin January 27, 2018 at 4:47 pm - Reply

    Very sweet n excellent teaching of excel..

  3. Boubacar diallo February 1, 2018 at 10:18 pm - Reply

    tremendous! Mrs Leila Gharani
    I really enjoyed your video.

  4. Robert Ward March 8, 2018 at 7:48 pm - Reply

    Question. 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?

  5. Stephen Guye March 20, 2018 at 7:13 pm - Reply

    I 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.

    • Shok July 13, 2018 at 12:51 am - Reply

      Hi Stephen, I have the same issue. Did you manage to find solution? Please let me know.

  6. Samuel March 28, 2018 at 10:29 am - Reply

    Your style of presentation is extremely good. Easy to grasp and attractive, too. Keep it up, ma’am!

    • Leila Gharani March 29, 2018 at 9:47 am - Reply

      Thank you! Glad you like the tutorials :)

  7. Sonya Ann Meinert June 11, 2018 at 1:51 am - Reply

    Very helpful and easy to understand thank you thank you thank you

    • Leila Gharani June 11, 2018 at 12:30 pm - Reply

      You’re very welcome Sonya. Glad you find it helpful.

  8. ikefresh June 11, 2018 at 3:37 pm - Reply

    I love your teachings, you made it so so simple

    • Leila Gharani June 12, 2018 at 7:15 am - Reply

      Thank you! I’m glad to hear that :)

  9. Marco June 11, 2018 at 8:37 pm - Reply

    I 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.

  10. Salion June 30, 2018 at 8:08 am - Reply

    Hi 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.

  11. Dee September 4, 2018 at 1:59 am - Reply

    thank 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

  12. Dee September 4, 2018 at 2:04 am - Reply

    thank 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

  13. Rosemberg Caicedo September 4, 2018 at 10:11 pm - Reply

    Hi 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 Caicedo September 4, 2018 at 10:13 pm - Reply

      sorry, 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

  14. Adam September 19, 2018 at 9:59 am - Reply

    Hi
    I just love the way you explain !!!
    AMAZING

  15. Ashish October 18, 2018 at 6:03 pm - Reply

    Hi. 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 Gharani October 23, 2018 at 8:44 am - Reply

      Hi Ashish, Thank you! Yes Index works for columns as well.

  16. anu November 28, 2018 at 4:34 pm - Reply

    Hi 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 Gharani November 29, 2018 at 7:52 am - Reply

      Hi – 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)).

Leave A Comment

Share This