+ Reply to Thread
Results 1 to 6 of 6

Formula to change column ref based on date and do index match

  1. #1
    Registered User
    Join Date
    08-19-2014
    Location
    Bournemouth, England
    MS-Off Ver
    Office 365
    Posts
    24

    Formula to change column ref based on date and do index match

    Hi all

    could someone please give me a hand with a formula which will match the date entered in range B1:G1 across J1:S1 and then do index match within an appropriate column to find a corresponding value for ID in column A?
    The shown below is a simplified version of the original file as both sets of data (B1:G1 and J1:S1) live in a separate worksheets.
    The data in column J1:S1 cannot be prepared as one maser ID and multiple daily values. This must be a criteria dependant match as described above. I have spent most of the Xmas day trying to resolve this problem, providing a solution would be a fantastic Xmas gift for me.

    Kind regards.

    excel2411.JPG
    Attached Images Attached Images
    Last edited by Marq555; 12-25-2014 at 03:48 PM.

  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: Formula to change column ref based on date and do index match

    in B2
    =INDEX(INDEX($J$2:$S$100,0,MATCH(B$1,$J$1:$S$1,0)+1),MATCH($A2,INDEX($J$2:$S$100,0,MATCH(B$1,$J$1:$S$1,0))))
    "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
    08-19-2014
    Location
    Bournemouth, England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Formula to change column ref based on date and do index match

    Hi martindwilson

    Thank you for your prompt reply.
    I have spent the last few hours trying to use the provided by you formula in the attached workbook, unfortunately it doesn't work, could you please take a look at the link below to see what I mean?

    https://drive.google.com/open?id=0B2...N3M&authuser=0

    excel2.JPG


    Many thanks

  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: Formula to change column ref based on date and do index match

    oops missing a 0 in last match
    =INDEX(INDEX($J$3:$Q$8,0,MATCH(B$1,$J$1:$Q$1,0)+1),MATCH($A2,INDEX($J$3:$Q$8,0,MATCH(B$1,$J$1:$Q$1,0)),0))
    Attached Files Attached Files
    Last edited by martindwilson; 12-25-2014 at 07:21 PM.

  5. #5
    Registered User
    Join Date
    08-19-2014
    Location
    Bournemouth, England
    MS-Off Ver
    Office 365
    Posts
    24

    Re: Formula to change column ref based on date and do index match

    Thank you so much, works like a charm!

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

    Re: Formula to change column ref based on date and do index match

    you could just use offset tho in b2
    =VLOOKUP($A2,OFFSET($J$1,0,MATCH(B$1,$J$1:$Q$1,0)-1,8,2),2,FALSE)

+ 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] I need an index match formula to find text in a column and a date in a row.
    By joshie78 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-25-2014, 03:48 AM
  2. [SOLVED] Index Match Based on One Date Criteria, Table Contains Beginning Date and End Date
    By jcox1953 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-09-2014, 02:41 PM
  3. Replies: 10
    Last Post: 07-12-2012, 12:13 PM
  4. Replies: 5
    Last Post: 01-06-2012, 08:00 PM
  5. INDEX MATCH and change column headings
    By thart21 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-22-2011, 11:19 AM

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