+ Reply to Thread
Results 1 to 6 of 6

Index/Match/Match. Formula looking veritcal and horizontal headings to find a match

  1. 02-29-2012, 07:46 PM #1
    rain4u
    rain4u is offline
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Index/Match/Match. Formula looking veritcal and horizontal headings to find a match

    Hi
    I have spend a lot of time to figure it out myself but I can not crack it. I wonder if someone can help me.

    There are two sheets:
    1) profiles_available
    2) profiles_selected

    On sheet profiles_available I have a following table
    Row1 Col A Col B Col C Col D Col E
    Row2 Standard wk B/H Mon noname xmas
    Row3 Sunday 2.31 10 8 0
    Row4 Saturday 11.27 10 11 0
    Row5 Friday 17.57 20 27 0
    Row6 Thursday 17.57 20 27 25
    Row7 Wednesday 17.57 20 27 25
    Row8 Tuesday 17.57 20 0 25
    Row9 Monday 16.14 0 0 25



    On sheet profiles_selected I have a following table
    Row1 Col B Col C Col D Col E
    Row2 Selected profile → xmas Standard wk B/H Mon
    Row3 Sunday
    Row4 Saturday
    Row5 Friday
    Row6 Thursday
    Row7 Wednesday
    Row8 Tuesday
    Row9 Monday







    Now I would like to have a formula that would help me to populate this empty table on sheet profiles_selected using the vertical and horizontal headings (in bold RED).
    Row1 Col B Col C Col D Col E
    Row2 Selected profile → xmas Standard wk B/H Mon
    Row3 Sunday 0 2.31 10
    Row4 Saturday 0 11.27 10
    Row5 Friday 0 17.57 20
    Row6 Thursday 25 17.57 20
    Row7 Wednesday 25 17.57 20
    Row8 Tuesday 25 17.57 20
    Row9 Monday 25 16.14 0



    I have attached spreadsheet with both sheets on it (plus example of before and after). If anyone can help me out I would appreciate this a lot.


    Cheers
    Attached Files Attached Files
    Last edited by rain4u; 02-29-2012 at 09:06 PM.
    Reply With QuoteRegister To Reply
  2. 02-29-2012, 07:56 PM #2
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Index/Match/Match. Formula looking veritcal and horizontal headings to find a mat

    Hi,

    The profiles selected table appears to be the same as the available table apart from the order of the columns. It's not immediately apparent to me why you even need a new table, but trivially can't you just link the cells directly?

    e.g. in C3 in the selected table
    =profiles_available!E3
    and copied down.

    create a similar formula in D3 & E3.

    If I've missed something significant then please explain further.

    Regards
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.
    Reply With QuoteRegister To Reply
  3. 02-29-2012, 08:25 PM #3
    rain4u
    rain4u is offline
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Index/Match/Match. Formula looking veritcal and horizontal headings to find a mat

    Hi Richard
    Thank you for quick reply.
    Both tables are fraction of the real spreadsheet and there is also some dynamic data involved. Let me put it this way, the table on profiles_available has currently near 20 different profiles (and it will be still growing over period of time). I only need 4-5 active profiles. Both of these tables were just an example. My intention is to use dynamic named ranges at later stage etc. I won't go into too much detail as otherwise it will become rather long and boring post. I just need to have the flexibility to change the header values of these 4-5 active profiles (there is validation drop down list on row 2) and get the figures changed on the fly accordingly.


    Thank you for showing interest.
    Any help is still appreciated.
    Reply With QuoteRegister To Reply
  4. 02-29-2012, 08:38 PM #4
    rylo
    rylo is offline
    Forum Expert
    Join Date
    01-15-2007
    Location
    Brisbane, Australia
    MS-Off Ver
    2007
    Posts
    6,591

    Re: Index/Match/Match. Formula looking veritcal and horizontal headings to find a mat

    Hi

    profiles_selected!c3: =OFFSET(profiles_available!$A$1,ROW()-1,MATCH(C$2,profiles_available!$2:$2,0)-1)

    See how that goes.

    rylo
    Reply With QuoteRegister To Reply
  5. 02-29-2012, 08:42 PM #5
    Richard Buttrey
    Richard Buttrey is offline
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Index/Match/Match. Formula looking veritcal and horizontal headings to find a mat

    Hi,

    If your original doesn't reflect the detail of the real workbook would you therefore upload at least a representative sample of your workbook.
    There's no point is us guessing what it looks like when an actual example should definitively explain it.

    It is most frustrating to attempt a solution only to discover we haven't been given the full story.

    Regards
    Reply With QuoteRegister To Reply
  6. 02-29-2012, 08:51 PM #6
    rain4u
    rain4u is offline
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: Index/Match/Match. Formula looking veritcal and horizontal headings to find a mat

    Rylo
    You're a star! Works as advertised. Thank you.

    Richard
    Thank you for your contribution. Uploading the whole spreadsheet would been too complicated for me. I thought I will try to keep it simple to begin with. I think I explained it sufficiently and gave enough examples and illustrations so at least Rylo was able to understand what I was after.


    Thank you both for helping me.
    Cheers
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1