Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Index Match multiple criteria (Excel 2003)

    I am trying to lookup the value in column E based on two criteria:

    That column B has the word “Adult” and column D has the word “Art”.

    I am struggling with Index/Match.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Index Match multiple criteria (Excel 2003)

    You can use something like the array (confirm with ctrl-shift-enter)

    =INDEX($E$2:$E$7,MATCH("AdultArt",$B$2:$B$7&$D$2:$ D$7,0))

    to look up the value in Col E matching Adult and Art...

    Steve

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Index Match multiple criteria (Excel 2003)

    You can use the following array formula (confirm with Ctrl+Shift+Enter):

    =INDEX(E2:E7,MATCH("Adult"&"|"&"Art",B2:B7&"|"&D27,0))

    if you enter the word Adult in H1 and Art in H2, you can use (still as an array formula)

    =INDEX(E2:E7,MATCH(H1&"|"&H2,B2:B7&"|"&D27,0))

    The string "|" is used as a separator; in this example you might omit it, but in some situations it can prevent confusion.

  4. #4
    Lounger
    Join Date
    Apr 2008
    Posts
    41
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index Match multiple criteria (Excel 2003)

    Thanks! Can I select the whole column as well as a range?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Index Match multiple criteria (Excel 2003)

    If you want to know whether you can replace B2:B7 with B:B etc., the answer is no. This kind of formula only works with a "finite" range. But you can make it larger than you'll probably need, e.g. B2:B60000 or so.

  6. #6
    2 Star Lounger
    Join Date
    Aug 2004
    Posts
    123
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Index Match multiple criteria (Excel 2003)

    1] Try this non-array formula :

    =INDEX($E$2:$E$7,MATCH(1,INDEX(("Adult"=$B$2:$B$7) *("Art"=$D$2:$D$7),0,0),0))

    2] And, this unusual non-array formula solution.

    =INDEX(B:E,MATCH(1,INDEX(("Adult"=$B$2:$B$7)*("Art "=$D$2:$D$7),0,1),)+1,0) E:E

    Regards
    Bosco

  7. #7
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Tampico, Tamps, Mexico
    Posts
    118
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Index Match multiple criteria (Excel 2003)

    Or
    =INDEX(E2:E7,MATCH("Adult"&"?"&"Art",B2:B7&"|"&D27,0))
    =INDEX(E2:E7,MATCH(H1&"?"&H2,B2:B7&"?"&D27,0))
    (confirm with Ctrl+Shift+Enter)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •