+ Reply to Thread
Results 1 to 7 of 7

MATCH within a MATCH within an INDEX?

  1. #1
    Registered User
    Join Date
    12-23-2011
    Location
    Rochester, NY
    MS-Off Ver
    Excel 2003
    Posts
    33

    Question MATCH within a MATCH within an INDEX?

    I've tried searching for this, but either it doesn't exist or I'm using the wrong terms.

    I'm trying to get a value from a table that resides next to a specific cell (the cell that says "Total") that is under-and-to-the-right of another cell (the category).

    Example:

    Fruits
    Strawberry 20
    Cherry 23
    Orange 13
    Total 36

    I want to get the 36 out of there. There are other sections that look like that, but I want the Fruits section. So I need to locate the Fruits section and then locate the Total row underneath Fruits to obtain the 36. I thought about using a MATCH within a MATCH and then an INDEX, but I can't figure it out. Also, the original table that I'm trying to extract the information from is not editable (at least not by me).

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: MATCH within a MATCH within an INDEX?

    try like this i may have overcomplicated it as it's late change dropdown in c1
    Attached Files Attached Files
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Registered User
    Join Date
    12-23-2011
    Location
    Rochester, NY
    MS-Off Ver
    Excel 2003
    Posts
    33

    Re: MATCH within a MATCH within an INDEX?

    That was quick! We're getting closer, I think. I forgot to mention that the categories go vertically, not horizontally. Also, the formatting of my original post did not work. I'm attaching what the table actually looks like. And the Fruits section can change positions, hence why I need a match/lookup function of some sort.

    example.xlsx

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: MATCH within a MATCH within an INDEX?

    well i dont usually advocate offset() but it works here
    Attached Files Attached Files

  5. #5
    Forum Expert azumi's Avatar
    Join Date
    12-10-2012
    Location
    YK, Indonesia
    MS-Off Ver
    Excel 2019
    Posts
    2,360

    Re: MATCH within a MATCH within an INDEX?

    Another try:

    =INDEX(C1:C22,MATCH(1,(LOOKUP(ROW(A1:A22),ROW(A1:A22)/(A1:A22<>""),A1:A22)=E1)*(B1:B22="total"),0))

    Array Formula (Press CTRL+SHIFT+ENTER button together)

    Or pls see the file
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    12-23-2011
    Location
    Rochester, NY
    MS-Off Ver
    Excel 2003
    Posts
    33

    Re: MATCH within a MATCH within an INDEX?

    azumi's solution worked. Thank you! I assume martindwilson's would have also, but I jumped right into azumi's so I can't say for sure. Thank you both!

  7. #7
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: MATCH within a MATCH within an INDEX?

    some times i wonder what the point is!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 6
    Last Post: 04-30-2014, 02:42 AM
  2. Replies: 6
    Last Post: 11-08-2013, 10:29 PM
  3. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  4. [SOLVED] Index/Match/Match....Stops without completing the match
    By irsles in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-14-2012, 10:16 PM
  5. Replies: 5
    Last Post: 02-29-2012, 08:51 PM

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