+ Reply to Thread
Results 1 to 4 of 4

VLOOKUP (multiple criteria) vs. INDEX and MATCH

  1. #1
    Registered User
    Join Date
    04-13-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    14

    VLOOKUP (multiple criteria) vs. INDEX and MATCH

    Hello all. New to the board. I'll get right to it...

    I'm trying to do VLOOKUP with multiple criteria (or maybe I should do INDEX or MATCH?). In the attachment, I want to place the argument in the cells C11 and C12.

    So that in Table 2, if you enter ABC in the "Letters" column and 6/1/2011 in the "Date" column, it knows to go into Table 1 and grab 15. If it's ABC and 6/5/2011, then it's "81", etc.

    I'm pasting the two tables from the attachment to the body of this post thought I'm not sure if it's going to be readable. Thanks!


    Table 1
    ABC DEF GHI
    6/1/2011 15 29 18
    6/3/2011 42 66 22
    6/5/2011 81 82 44


    Table 2

    Letters Date Amount
    ABC 6/1/2011 ???
    ABC 6/5/2011 ???
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    12-15-2009
    Location
    Chicago, IL
    MS-Off Ver
    Microsoft Office 365
    Posts
    3,177

    Re: VLOOKUP (multiple criteria) vs. INDEX and MATCH

    Try

    =SUMPRODUCT(($B$2:$D$2=A11)*($A$3:$A$5=B11)*$B$3:$D$5) and scroll it down

  3. #3
    Forum Expert
    Join Date
    09-27-2011
    Location
    Poland
    MS-Off Ver
    Excel 2007
    Posts
    1,312

    Re: VLOOKUP (multiple criteria) vs. INDEX and MATCH

    or
    =INDEX($A$2:$D$5,MATCH(B12,$A$2:$A$5,0),MATCH(A12,$A$2:$D$2,0))
    Regards

    tom1977

    If You are satisfied with my solution click the small star icon on the left to say thanks.

  4. #4
    Registered User
    Join Date
    04-13-2012
    Location
    New York, NY
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: VLOOKUP (multiple criteria) vs. INDEX and MATCH

    Thanks Ton and Jie! Both solutions worked. (Should've come here a couple hours ago.) Cheers.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1