+ Reply to Thread
Results 1 to 8 of 8

Index and Match Multiple Header (Rows) Criteria

  1. #1
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Thumbs up Index and Match Multiple Header (Rows) Criteria

    Attachment 150008Hello Excel Gurus,

    I have a simple question for you! (It's hard to me though).

    Below is my data.
    B1-E1: Gross Revenue (Merged Cell)F1-I1: Net Revenue (Merged Cell)
    A2: Material B2: Q1 C2: Q2 D2: Q3 E2: Q4 F2: Q1 G2: Q2 H2: Q3 I2: Q4
    A3: XYZ B3: 100 C3: 125 D3: 150 E3: 125 F3: 80 G3: 90 H3: 100 I3: 85

    So basically, What I need is that, through the formula, Index, Match, Vlookup etc, whatever is the most effective and efficient way, When the material is XYZ for Gross Revenue Q3, the value return should be 150. For material XYZ for Net Revenue Q2, the answer is 90

    ps: I've tried to make it look good in here, but you can't understand what I'm trying to do, I have an attached spreadsheet.

    Uploaded Spreadsheet: Multiple Header Criteria.xlsx
    Last edited by dluhut; 04-12-2012 at 01:41 PM. Reason: Update Spreadsheet

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index and Match Multiple Header (Rows) Criteria

    If your inputs: Material, Revenue type, quarter are in N1:N3, respectively, then try:

    =INDEX(INDEX($B$3:$I$3,MATCH(N2,$B$1:$I$1,0)):$I$8,MATCH(N1,$A$3:$A$8,0),MATCH(N3,INDEX($B$2:$I$2,MATCH(N2,$B$1:$I$1,0)):$I$2,0))
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Index and Match Multiple Header (Rows) Criteria

    NBVC,

    Thanks for helping. I should have more specific in terms of how the "result" is going to look at. I've updated the spreadsheet, and hopefully it'll be more understandable for you.

    In short, the format of is going to be the same too...where the rows have 2 headers, the gross revenue and net revenue merged, and below of each were the quarters. As well, the left hand column would be the material.

    PS: Notice that I Puposely make the the "Net Revenue" to appear first and the material to be descending.
    Last edited by dluhut; 04-12-2012 at 11:47 AM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index and Match Multiple Header (Rows) Criteria

    In B15:

    Please Login or Register  to view this content.
    copied down and across.

  5. #5
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Index and Match Multiple Header (Rows) Criteria

    Thanks NBVC!!! You're indeed a Guru in Excel.

    Just wondering if you could explain how the formula works?

    ps: I've added your reputation. Hope you received it. Thanks, once again.

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Index and Match Multiple Header (Rows) Criteria

    It's basically a regular Index/Match function with some nested Index/Matches to determine where to start indexing from based on the Revenue type header.

    This part: INDEX($B$3:$I$3,MATCH(LOOKUP(REPT("z",255),$B$13:B$13),$B$1:$I$1,0)):$I$8 determines which of the revenue groups to look in. The LOOKUP(REPT("z",255),$B$13:B$13) parts looks for the last text string as you copy across cells in row 13 (notice as you copy across, $B$13:B$13 becomes $B$13:C$13, $B$13:D$13 and so on). All cells in columns B:D will reference the revenue in B13 and all cells in column E:I will reference the revenue in E13... In this way all cells under the merged headers will reference the corresponding header.

    Once that header string is determined, the MATCH() finds the same header in B1:I1 and that is Indexed to same position in B3:I3 and that is the "upper left" corner of your range. The I8 is fixed as the "bottom right" corner.

    Similar for the other MATCH() function. Once the start is determined, then the rest is a normal INDEX/MATCH formula.

  7. #7
    Valued Forum Contributor
    Join Date
    04-13-2011
    Location
    Ottawa, Canada
    MS-Off Ver
    365
    Posts
    1,067

    Re: Index and Match Multiple Header (Rows) Criteria

    Although I still completely do not understand, but will take the time to read, digest, over and over again. Hopefully will understand it one day.

    Thanks once again.

  8. #8
    Forum Guru
    Join Date
    05-24-2011
    Location
    India
    MS-Off Ver
    Office 2021
    Posts
    2,237

    Re: Index and Match Multiple Header (Rows) Criteria

    You could also shorten with HLOOKUP.

    Please Login or Register  to view this content.
    copy across & down.
    Regards,
    Haseeb Avarakkan

    __________________________________
    "Feedback is the breakfast of champions"

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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