+ Reply to Thread
Results 1 to 12 of 12

Double layered INDEX/MATCH?

  1. #1
    Registered User
    Join Date
    01-02-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    4

    Double layered INDEX/MATCH?

    Hello everyone

    I'm trying to extract data from a table that has two layers of horizontal and vertical headings.

    I know how to find a value using INDEX(MATCH,MATCH) if there is only 1 horizontal and vertical heading but am stumped with two!

    Example data attached.

    An example of a top layer horizontal heading would be 'Visits', with the layer below splitting this into country names.

    An example of a top layer vertical heading would be '2013|45' (year and week), with the layer below splitting this down to source type (i.e. email, organic, etc).

    Unfortunately I'm not able to adjust the layout of the source data as it is exported from Google Analytics using an export tool.

    I want to be able to MATCH the first layer then the second, i.e. provide the value for 'Pagviews' from 'United States', for week '2013|45' from 'Organic' only.

    Appreciate any help!
    Attached Files Attached Files

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Double layered INDEX/MATCH?

    Assuming that the layers are following a pattern like

    1 A
    1 B
    1 C
    2 A
    2 B
    2 C

    Same horizontally..

    See how this works. (I filled in the last row to complete the pattern)
    EFkuni-d.xlsx

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Double layered INDEX/MATCH?

    This will work for you if you create a small table like this...
    A
    B
    19
    Visits
    16
    20
    Saudi Arabia
    21
    2013|45
    22
    email


    B19=OFFSET($A$1,(MATCH($A$21,$A$1:$A$14,0)-1)+(MATCH($A$22,$B$1:$B$14,0)-3),(MATCH($A$19,A1:AB1,0)-1)+MATCH($A$20,$C$2:$O$2,0)-1)

    You could simplify things by creating Drop-Downs for each category

    edit: @ Jonmo, very similar approach
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Double layered INDEX/MATCH?

    Probably look something like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Regards, TMS
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  5. #5
    Registered User
    Join Date
    01-02-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Double layered INDEX/MATCH?

    Hi Jonmo1, thanks for replying.

    The second layer isn't always in the same order + it doesn't always have the same values, so it could be...

    1 A
    1 B
    1 C
    2 A
    2 D
    2 B

    Would this make a difference?

  6. #6
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Double layered INDEX/MATCH?

    Yes, that makes a big difference.

    What about the horizontal layers (Visits/Pageviews and Country) ?

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Double layered INDEX/MATCH?

    Shame the example just repeated the data range then.

    You can throw my solution in the bin. If the layers are not always in the same order and don't contain the same values, presumably there may be more or less locations. How then, can we determine the start and end of each of the ranges?

    Regards, TMS

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Double layered INDEX/MATCH?

    I think my suggestion may still work, but it would need to be tested on your actual data (assuming the countries at least, are the same?)

  9. #9
    Registered User
    Join Date
    01-02-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Double layered INDEX/MATCH?

    Sorry, I should have been clearer with my example.

    I have attached a sample spreadsheet which more accurately resembles what I'm trying to work with.

    SOURCE sheet is sample data in the format I receive it.

    Horizontal:

    Top layer - is always in the same order - visits, new visits %, pageviews, etc
    2nd layer - will always have the same countries, but the column order can shift when the data is refreshed from source. When the columns for a country shift, this happens in the same manner for the same grouping of countries under each top layer item.

    Vertical:

    Top layer - is always in order of year-week descending
    2nd layer - new source types feature depending upon data from google, so sometimes this 2nd layer has 5 rows and othertimes 10. There does appear to be an order (i.e. '(none)' always comes first, etc), but as a particular one may not feature in the next year-week set, I'm not sure if this pattern can be used.

    The 'DESTINATION' sheet shows you what I'm trying to do.

    For each country (i.e. United Kingdom), I'm trying to extract specific 'medium' values (i.e. 'affil') for a specific 'metric' (i.e. Visits) for a specific year-week (i.e. 2013 48). In this example the correct value would be 2089.

    I have put the medium values I am interested in querying across the top, under the country/metric headings. I'm trying to build a forumula which uses these values, so that I can copy the formula across and down the sheet.

    Thank you
    Attached Files Attached Files

  10. #10
    Registered User
    Join Date
    01-02-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2013
    Posts
    4

    Re: Double layered INDEX/MATCH?

    Really appreciate anyones help on this

    Still stumped!

  11. #11
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Double layered INDEX/MATCH?

    On the Source Page, Row 1.
    Can you get rid of the Merged Cells, and put the appropriate words in each cell (Visits | Visits | New visists % | etc...)

  12. #12
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Double layered INDEX/MATCH?

    If you can do that, then it's a fairly simple sumproduct.

    EFkuni-d2.xlsx


    Allthough, looking at your file.
    I would actually prefer the original layout of the data you already have on the Source Page...

+ 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. [SOLVED] INDEX MATCH SMALL ROW, Double criteria Lookup
    By GP_SRT in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 11-22-2013, 11:15 AM
  2. Double lookup or index and match?
    By dan_manchester in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2013, 09:01 AM
  3. VBA - Index double match and insert values in range
    By Biinge in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-02-2011, 02:43 PM
  4. Double VLookup or INDEX MATCH or something else?
    By carlwin in forum Excel General
    Replies: 5
    Last Post: 06-20-2010, 08:00 PM
  5. dynamic, double vlookup, match, index, dget?? different workbooks
    By Leslie in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-05-2005, 11:05 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