+ Reply to Thread
Results 1 to 8 of 8

Index and three match query

  1. #1
    Registered User
    Join Date
    05-28-2013
    Location
    England
    MS-Off Ver
    Excel 2009
    Posts
    7

    Index and three match query

    This is what I'm trying to do:

    Formula.jpg


    So Warrington shows data which I've entered in manually, but that's what I want to formulate, my query is close I think. =INDEX(E16:H18,MATCH(A23,B16:B18,0),MATCH(D22,C16:C18,0), MATCH(B23,E15:H15,0))


    Thanks In Advance
    Ajayd3v
    Last edited by ajayd3v; 05-28-2013 at 09:01 AM.

  2. #2
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index and three match query

    Both of your attached screencaps are reported as "Invalid Attachments".

    Try attaching a SMALL sample file. Make sure you tell us what result you expect.
    Last edited by Tony Valko; 05-28-2013 at 09:36 AM. Reason: I don't know how to spell
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Registered User
    Join Date
    05-28-2013
    Location
    England
    MS-Off Ver
    Excel 2009
    Posts
    7

    Re: Index and three match query

    Quote Originally Posted by Tony Valko View Post
    Both of your attached screencaps are reported as "Invalid Attachments".

    Try attaching a SMALL sample file. Make sure you tell us what result you expect.

    http://www.excelforum.com/attachment...4&d=1369746047

    The above link is the attachment.

    Thanks In Advance
    Ajayd3v

  4. #4
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index and three match query

    Ok, how about posting that file instead of a screencap?

    That way I don't have to recreate your data.

  5. #5
    Registered User
    Join Date
    05-28-2013
    Location
    England
    MS-Off Ver
    Excel 2009
    Posts
    7

    Re: Index and three match query

    Quote Originally Posted by Tony Valko View Post
    Ok, how about posting that file instead of a screencap?

    That way I don't have to recreate your data.
    Sorry about that, didn't know it was possible to attach a spreadhsheet: Excel spreadsheet: Example.xlsx

    Thanks In Advance
    Ajayd3v

  6. #6
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index and three match query

    Change the column header in H4 from Rate3 to Rate 3.

    Enter this formula in E12:

    =SUMIFS(INDEX($F$5:$I$8,,MATCH($D12,$F$4:$I$4,0)),$C$5:$C$8,$C$12,$D$5:$D$8,E$11)

    Copy across to F12 then down to E14:F14.

    Use the same syntax for the WARRINGTON data.

  7. #7
    Registered User
    Join Date
    05-28-2013
    Location
    England
    MS-Off Ver
    Excel 2009
    Posts
    7

    Re: Index and three match query

    Quote Originally Posted by Tony Valko View Post
    Change the column header in H4 from Rate3 to Rate 3.

    Enter this formula in E12:

    =SUMIFS(INDEX($F$5:$I$8,,MATCH($D12,$F$4:$I$4,0)),$C$5:$C$8,$C$12,$D$5:$D$8,E$11)

    Copy across to F12 then down to E14:F14.

    Use the same syntax for the WARRINGTON data.
    Cheers mate, that worked perfectly :D

  8. #8
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Index and three match query

    You're welcome. Thanks for the feedback!

+ 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