+ Reply to Thread
Results 1 to 3 of 3

Helped needed to index match row header, column header, and sheet

  1. #1
    Registered User
    Join Date
    08-12-2016
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    2

    Helped needed to index match row header, column header, and sheet

    Hi

    This is my first time posting here, I need some specific help on matching different row column and sheet name (I keep getting error returned if I try to combine too many formula)

    On the attached image, Master Sheet has categories in row/column, state header and Month header

    I want cell B4 of maste rfile, to return the value from individual sheet state name (matching Row 1). where in the sheet, column A have same row header, A4 (Master). row 2 with same column header "B3" (Master) and row 1 with same month code A2 (Master)

    I currently use index match to return with this portion (column A have same row header, A4. row 2 with same column header "B3"), but our actual file has all states and numerous categories that I fear manual change on different columns may cause error, would it be possible to have a formula that return with the above criterias?

    Thank you sooooo much for your help! (The attached image portion was a bad screenshot which I can't figure out how to remove)

    Master File
    Master Sheet.PNG

    Individual Sheet
    Individual Sheet.PNG
    Attached Images Attached Images
    Last edited by vw103604; 08-12-2016 at 12:37 PM.

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 2016 (Windows)
    Posts
    5,352

    Re: Helped needed to index match row header, column header, and sheet

    This assumes that the sheets are in the same workbook. In cell B5, use the formula:

    =INDEX(INDIRECT(B$1 & "!A1:ZZ100"),MATCH($A5,INDIRECT(B$1 & "!A:A"),FALSE),MATCH($A$2,INDIRECT(B$1 & "!1:1"),FALSE)+(MOD((COLUMN()-2),3)))

    And copy to all the other cells where you want data. If you have more than 100 rows of data and 702 columns, change A1:ZZ100 to accommodate your actual data sets.
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Registered User
    Join Date
    08-12-2016
    Location
    California, USA
    MS-Off Ver
    2010
    Posts
    2

    Re: Helped needed to index match row header, column header, and sheet

    Thank you very much!

+ 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: 3
    Last Post: 01-18-2016, 10:18 AM
  2. Using Lookup? Index match? to populate cells based on column header
    By NoExcelSkills17 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 01-15-2015, 03:50 PM
  3. [SOLVED] Need help with INDEX(), MATCH() issue over a field of data to retreive column header...
    By bbernzy in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-26-2014, 06:47 PM
  4. [SOLVED] Index & Match for multiple Column and Row to find header...
    By mr-c in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-31-2014, 08:24 AM
  5. Replies: 1
    Last Post: 10-05-2013, 03:35 PM
  6. Return Column Header from Index/Match function
    By del24ie in forum Excel General
    Replies: 2
    Last Post: 01-12-2012, 11:57 AM
  7. Index/Match Function to Return column header
    By djmarsh51 in forum Excel General
    Replies: 2
    Last Post: 05-07-2010, 02:10 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