"double" INDEX & MATCH search?
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: "double" INDEX & MATCH search?

  1. #1
    Board Regular
    Join Date
    Apr 2007
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default "double" INDEX & MATCH search?

    to all

    I am trying to do a "double" search with Index & MATCH
    I want to return the value in the header (row 7) corresponding to the max value - See attached test
    I find the max value - cell c2
    and find the ID corresponding to the max value (cell c3) using a match
    I know would like to return the case (row 7) corresponding to the max value & ID - in the test v5

    This is a test but I'd any (row) range dynamic. Is this possible with INDIRECT,INDEX & MATCH?

    How can this be done?

    Thanks in advance

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,050
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    2 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    "See attached test"

    FYI
    You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

    https://www.mrexcel.com/forum/about-...tachments.html

  3. #3
    Board Regular
    Join Date
    Apr 2007
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    BCDEFGHIJK
    1
    2max =0.933328
    3ID =78
    4case =return the  value in row 7 corrpsonding to the max value for the ID found. In this case v5
    5
    6
    7IDmaxv1v2v3v4v5
    810.9093360.5946340.3565690.9093360.8134210.649909
    920.8858650.068910.8858650.4721140.3236920.595647
    1030.4913320.0867350.1642880.462220.0135180.491332
    11780.9333280.5577310.6597970.8175640.7973750.933328
    12990.9189750.1922140.5126690.9103710.2014460.918975
    13

    Sheet1



    Worksheet Formulas
    CellFormula
    C2=MAX(C8:C12)
    C3=INDEX(B8:B12,MATCH(C2,C8:C12,0))
    C8=MAX(E8:I8)
    C9=MAX(E9:I9)
    C10=MAX(E10:I10)
    C11=MAX(E11:I11)
    C12=MAX(E12:I12)


  4. #4
    Board Regular
    Join Date
    Apr 2007
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    Had a go with one of the tools. See posted HTML above - A pain that one cannot attach xls directly anymore!
    Last edited by jxb; Jul 12th, 2019 at 12:40 PM.

  5. #5
    MrExcel MVP
    Junior Admin
    Joe4's Avatar
    Join Date
    Aug 2002
    Posts
    50,086
    Post Thanks / Like
    Mentioned
    52 Post(s)
    Tagged
    11 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    A pain that one cannot attach xls directly anymore!
    It never was possible.
    TIPS FOR FINDING EXCEL SOLUTIONS
    1. Use the built-in Help that comes with Excel/Access
    2. Use the Search functionality on this board
    3. A lot of VBA code can be acquired by using the Macro Recorder.

    "Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"

  6. #6
    Board Regular
    Join Date
    Apr 2007
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    Well it's been while and I am clearly going old then... Only managed to figure out about the copy-paste html thing!
    Let's see if someone can get a smart way of extracting the data
    Last edited by jxb; Jul 12th, 2019 at 12:51 PM.

  7. #7
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    How about
    =INDEX(E7:I7,SUMPRODUCT((E8:I12=MAX(E8:I12))*COLUMN(E8:I12))-COLUMN(E8:I12)+1)
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,345
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    Two more options:

    =INDEX(E7:I7,MATCH(C2,INDEX(E8:I12,MATCH(C2,C8:C12,0),0),0))

    which assumes the C8:C12 values are present. Or

    =INDEX(E7:I7,AGGREGATE(15,6,(COLUMN(E8:I12)-COLUMN(E8)+1)/(E8:I12=C2),1))

    which just reads the E8:I12 values directly.

    Note that in the unlikely event of a tie of the maximum value, Fluff's formula will return an error or an incorrect value, while these formulas will return the first match.
    Last edited by Eric W; Jul 12th, 2019 at 01:20 PM.
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #9
    Board Regular
    Join Date
    Apr 2007
    Posts
    104
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    Thanks. Will take time to breakdown the formula to understand it. In the meantime I'll definitely save it for future use

    Q: Is there not a way of only working on the row where the ID has been found? In other word I want to make use of the fact that I know the ID to work with
    Why: For the test the selecting the whole array is fine I ultimately will have a number of max/ID/case to be found each wording on a subset of the data set. See example below
    Mind you the test i just did based on your formula seems to work, so maybe I am over-thinking the whole think (as your solution seem to do the trick)

    BCDEFGHI
    2max1=0.933328
    3ID1 =78
    4case =v5return the  value in row 7 corrpsonding to the max value for the ID found. In this case v5
    5max2=0.994172
    6ID1 =103
    7case =v4
    8
    9
    10IDmaxv1v2v3v4v5
    1110.9093360.5950.3570.9090.8130.650
    1220.8858650.5650.8860.4720.3240.596
    1330.87970.0870.1640.8800.0140.491
    14780.9333280.5580.6600.8180.7970.933
    15990.9189750.1920.5130.9100.2010.919
    161010.985620.9860.6370.3940.0410.774
    171020.8374320.5570.2160.1540.6880.837
    181030.9941720.2350.7960.5500.9940.234
    191040.9502870.6130.4800.5560.9500.056

    Sheet1



    Worksheet Formulas
    CellFormula
    C2=MAX(C11:C15)
    C3=INDEX(B11:B15,MATCH(C2,C11:C15,0))
    C4=INDEX(E10:I10,SUMPRODUCT((E11:I15=MAX(E11:I15))*COLUMN(E11:I15))-COLUMN(E11:I15)+1)
    C5=MAX(C16:C19)
    C6=INDEX(B16:B19,MATCH(C5,C16:C19,0))
    C7=INDEX(E10:I10,SUMPRODUCT((E11:I19=MAX(E11:I19))*COLUMN(E11:I19))-COLUMN(E11:I19)+1)
    C11=MAX(E11:I11)
    C12=MAX(E12:I12)
    C13=MAX(E13:I13)
    C14=MAX(E14:I14)
    C15=MAX(E15:I15)
    C16=MAX(E16:I16)
    C17=MAX(E17:I17)
    C18=MAX(E18:I18)
    C19=MAX(E19:I19)

    Last edited by jxb; Jul 12th, 2019 at 01:30 PM.

  10. #10
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    25,638
    Post Thanks / Like
    Mentioned
    445 Post(s)
    Tagged
    45 Thread(s)

    Default Re: "double" INDEX & MATCH search?

    Eric's 1st formula will just look at the relevant row.
    Also as he pointed out, if it's possible to have the same value twice, my suggestion will fail
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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