Tom’s Tutorials For Excel: Intersecting Lookup With Multiple Criteria

Tom’s Tutorials For Excel: Intersecting Lookup With Multiple Criteria

I received an interesting comment and request from a vistor named lohhw3, who asked a follow-up question about this tutorial for intersecting lookups. The question was how to look up a value in a table when there are two columns of criteria (Operations and Year in this example) in addition to the row header criteria for Month.



In the picture, two columns need to be considered in order to determine the correct row. With the criteria specified in range A24:C24, the formula in cell A27 that returns the correct value is
=INDEX(A1:N19,MATCH(1,(A1:A19=A24)*(B1:B19=B24),0),MATCH(C24,$A$1:$N$1,0))

Note that this is an array formula, meaning you must commit it to the cell by pressing Ctrl+Shift+Enter, not just with Enter.

Thanks to lohhw3 for the excellent question!

Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
8 comments on “Tom’s Tutorials For Excel: Intersecting Lookup With Multiple Criteria
  1. Ricardo says:

    Hi there,

    What happens when we have 2 column headers? is it the same procedure? Thanks

  2. CecilA says:

    Hello Tom,

    Using the same table you have, I want the sum of office rent and utilities and I created and defined name for this as admin expense. so as oppose to using =A24, I can use a defined name. how would the formula look like. I still have the same criteria for the column.
    {=sum(sumif(sheet1$A$A,_adminexpense,sheet1!$G:$G))} . Here, I want to change the $g:$G to match the criteria, to July (which would be from a different column.
    AdminExpense is a group of different operating expense.
    Desired result:
    May July October
    Admin Expense
    Maintenance &Landscaping

    • Tom Urtis says:

      Would/Could this be any combination of months? Maybe 1 month or 2 months or 5 months, each time being different? How would you want to set the criteria (that is, have the formula know) for which month(s) you want to return a result for?

  3. CecilA says:

    The months would have its own column. basically I am creating another report that will show

    admin Expense (comprised of office rent and utilities) For any month that I choose. so if I choose May, it will give me the result for the month of May, so if I change the criteria which will be in a different column such as July, it will give me the admin expense for July.
    I have a separate list of operating expense and defined name for the expenses that fall under admin expense. I would like to use the defined name (the result will be the sum of what was defined in that name) in the row criteria.

  4. MHAYEE says:

    CAN YOU PLEASE HELP ME WITH THIS…

    STOCKNO. DEL DATE QTY
    P013-8737A 10/11/2016 2
    P013-8737A 10/11/2016 2
    P013-8737A 10/11/2016 2
    P013-8737A 10/11/2016 1
    P013-8737A 10/14/2016 2
    P013-8737A 10/12/2016 3
    P013-8910P 9/27/2016 2
    P013-8910P 11/12/2016 2
    P013-8910P 9/8/2016 1
    P013-8910P 9/27/2016 1
    P013-8910P 9/27/2016 1
    P013-8910P 9/27/2016 1
    P013-8910P 9/27/2016 1
    P013-8910P 9/27/2016 1
    P013-8910P 10/14/2016 1
    P013-8910P 10/24/2016 1
    P013-8910P 10/28/2016 1

    I JUST WANT TO GET THE DELIVERY DATE WITH THE HIGHEST QTY OF EACH STOCK NUMBER.

    THANKS:)

Leave a Reply

Your email address will not be published. Required fields are marked *

*