Results 1 to 11 of 11
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Using Index & Match Formula

    I have data on Sheet1 and the following formula on sheet "Summary"

    =IF(C$15="","",INDEX(Sheet1!$145:$145,MATCH(C$15,S heet1!$1:$1,FALSE)))

    The formula gives with the desired results from Oct 2009 to Feb 2010, except from March 2010 to sept 2010.

    This is baffling me. I have attached my spreadsheet. It would be appreciated if someone could assist me to let me know what is causing the problem so that I get the correct solution

    Your assistance will be most appreciated


    Regards

    Howard
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Dec 2009
    Location
    East Coast, USA
    Posts
    993
    Thanks
    8
    Thanked 43 Times in 43 Posts
    Have not looked at attachment, but, it looks like there is a space between S and h in the second use of Sheet in formula.

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Using Index & Match Formula

    Thanks for the reply. The formula has no space betwween the S and H in Sheet-I put a space in by error, before cvopying the formula.

    =IF(C$1="","",INDEX(Sheet1!$145:$145,MATCH(C$1,She et1!$1:$1,FALSE)))

    I still get eror results

    Your assistance in finding as solution will be most appreciated

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Could you upload a new copy of the file?

    When I try opening it I get an error that the compressed file is invalid or corrupted...

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts

    Using Index & Match Formula

    Hi Steve

    Attached please find new upload (unzipped). hiope this file is ok to open. Tested on my side before uploading and it opens without any problem

    Your assistance will be most appreciated
    Attached Files Attached Files

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Nope still no luck. I even tried on my work computer. The archives seem corrupted.

    Can you open the file that was uploaded?

    Steve
    PS could you upload a non-zipped copy?
    Last edited by sdckapr; 2011-01-22 at 14:37. Reason: added PS

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Steve

    I have managed to open the uploaded file. I have attached an unzipped copy. Hopefully you will be able to open this. If not I can email this directly to you
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Steve

    I have uploaded unzipped file so that you can check what is causing the error

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Match finds the FIRST item matching. Unhide cols F-K and you will see dupes of those dates which are the cols XL finds...

    Steve

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,423
    Thanks
    126
    Thanked 5 Times in 5 Posts
    Hi Steve

    Thanks for the help. This really stumped me. I would never have thought of unhiding columns to locate the problem

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    Unhiding the columns did not lead me to problem. My technique was to start with you formula
    =IF(C$1="","",INDEX(Sheet1!$145:$145,MATCH(C$1,She et1!$1:$1,FALSE)))

    and note what it was doing. The error was coming from the lookup so I copied the MATCH part:
    =MATCH(C$1,Sheet1!$1:$1,FALSE)

    in an empty row in each of the columns. I then looked to see what columns it was MATCHING. I noticed the "problem cols" were hidden, so I unhid them and noticed the duplicate entries of the dates...

    Steve

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •