+ Reply to Thread
Results 1 to 7 of 7

Offset, Index, Match formula with dates inconsistently working

  1. #1
    Registered User
    Join Date
    09-28-2010
    Location
    Middle East
    MS-Off Ver
    Excel 2003
    Posts
    66

    Offset, Index, Match formula with dates inconsistently working

    Hello

    Formula seems to work inconsistently mat be due to it being too big, or there is some conflict that arises in the dates????

    Could someone help with the formula - this would be hugely appreciated.

    See attached actual example.

    Refer Er Revised CAPEX Sheet (Result Data)

    Cell I4=IFERROR(INDEX(OFFSET('Ericsson PIP Date Input'!$C$2, MATCH($H4,'Ericsson PIP Date Input'!$C$3:$C$597,0), 1, 16,), MATCH(I$1, OFFSET('Ericsson PIP Date Input'!$C$2, MATCH($H4, 'Ericsson PIP Date Input'!$C$3:$C$597, 0), MATCH(MAX(OFFSET('Ericsson PIP Date Input'!$C$2, MATCH($H4, 'Ericsson PIP Date Input'!$C$3:$C$597,0)-1, , , 43)), OFFSET('Ericsson PIP Date Input'!$C$2,MATCH($H4, 'Ericsson PIP Date Input'!$C$3:$C$597,0)-1, , , 43), 0)-1, 16, ), 0)), "")

    This formula is picking-up data from the source table in Ericsson PIP Date Input (Source Data)

    What the formula is doing
    It picks-up the characteristic in Column D of Source Data (example ADV) and returns it to the Result sheet in the correct date as defined by I$1 and for the right Purchase Order descriptor $H4. Each new set of revised dates are entered into the Table and these are the dates that push the characteristic into the right date in the result sheet.

    Problem
    It seems to work inconsistently. I cannot copy and paste formulas into the revised dates table (say Cell F21). The characteristics in the Results disappear. It should push the characteristics in Results into the most recent dates as defined by the most up to date set of dates with the Revision Date in the yellow row. Sometimes if I copy the dates across from left to right and then change the dates it works, but not always;

    For example, Refer Row 5 in Er Revised CAPEX Sheet (Result Data). It is missing a number of the characteristics such as "CoReady" etc. This relates to the dates entered into Cell F21 in Ericsson PIP Date Input (Source Data).

    I thought it was because the formula was not looking forward to the most recent set of dates, but I don't think this is the problem as I seemed to overcome it for one the examples.

    Thanks in advance.

    David
    Attached Files Attached Files
    Last edited by David Brown; 03-31-2013 at 12:13 PM. Reason: Forgot to say thanks

  2. #2
    Registered User
    Join Date
    09-28-2010
    Location
    Middle East
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Offset, Index, Match formula with dates inconsistently working

    Is this proving too difficult??
    David

  3. #3
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Offset, Index, Match formula with dates inconsistently working

    Part of the problem is your dates are inconsistent, Its trying to find a match for 01-06-2013 (for example), and your date in the 'Ericsson PIP Date Input is 20-06-2013, which is not a match, so it errors, which returns a blank cell
    Either change the date in the PIP Input table to same date as being looked for (01-06-2013) , this is probably the easiest route,
    or the match formula will need to be changed to look at month and year only

    Hope this helps

    Edit-
    As soon as I changed the (few) dates that were not 01-xx-xxxx, the table seemed to fill out properly
    Last edited by dredwolf; 03-31-2013 at 02:44 PM.
    A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
    -Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
    -To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
    If you received helpful response, please remember to hit the * of that post

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux - O365
    Posts
    12,743

    Re: Offset, Index, Match formula with dates inconsistently working

    Quote Originally Posted by David Brown View Post
    Is this proving too difficult??
    David
    What do you mean?

  5. #5
    Registered User
    Join Date
    09-28-2010
    Location
    Middle East
    MS-Off Ver
    Excel 2003
    Posts
    66

    Re: Offset, Index, Match formula with dates inconsistently working

    Hello
    Thanks for this. How embarrassing!
    Can you tell me, if I want to be able to enter into the Data Table (PIP Input) using the date format 20/06/2013 (instead of 01/06/2013), do I need to change the match date format in the results table to the format 30/06/2013? Since 20/06/2013 < 30/06/2013 will it work?
    Thanks/
    David

  6. #6
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Offset, Index, Match formula with dates inconsistently working

    No, your formula is looking for exact matches, therefore the dates need to be the same, the other option I mentioned would be to change the match formulas to only look for month and year, I'm not sure how difficult that would be, you may even have to change the formula to an array formula to make it work..I will mess around with it a bit and see what I can come up with

  7. #7
    Forum Expert dredwolf's Avatar
    Join Date
    10-27-2012
    Location
    Clearwater,Canada
    MS-Off Ver
    Excel 2007
    Posts
    2,649

    Re: Offset, Index, Match formula with dates inconsistently working

    Okay, I still think altering the Input data is easier, But either of these 2 Array Formulas (Cntrl+Shift+Enter, not just Enter) in I4 of 'Er Revised CAPEX' sheet:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

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

    Drag down and across

    First one compares Months and Years, second one forces the input sheet data to the 1st of the month for the formula

    Hope this helps

+ 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