+ Reply to Thread
Results 1 to 5 of 5

Index Match formula doesn't seem to be working

  1. #1
    Registered User
    Join Date
    02-17-2013
    Location
    Portland, Oregon
    MS-Off Ver
    2010
    Posts
    2

    Index Match formula doesn't seem to be working

    Hi all. I work a decent amount with Index Match formulas and I'm really stumped on this one. I have a colum with each cell containing a simple formula that displays the number 1 if the value to the right of the cell is greater than zero or displays 0 if the cell to the right is "". The result of this formula in the column is that I have a count of each cell with a number value in it and ecentially skipping over the cells without a number value and resuming the count when numbers show back up.

    My question is related to another sheet that is using an Index-Match formula to match numbers 1-3000 or so accross columns (attached is only portion of this sheet) to the numbers mentioned above. I cannot get this formula to work: =INDEX($N$2:$N$43,MATCH(T2,$O$2:$O$43),0). I'm sure it has to do with the formula I have in the "Match" colum with contains both numbers and zeros. My understanding of this formula is obviously incorrect in that is seems to me that it should look for the value I am specifing and skip over zeros to find it in the rainge I've specified. The value this formla is giving me is simply blank.

    Any help would be greatly appreciated. Attached is the file.

    David
    Attached Files Attached Files

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    Microsoft 365
    Posts
    14,695

    Re: Index Match formula doesn't seem to be working

    Hello David, you have the penultimate parenthesis in the wrong place, it needs to be like this

    =INDEX($N$2:$N$43,MATCH(T2,$O$2:$O$43,0))

    so that the zero is within the MATCH function
    Audere est facere

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

    Re: Index Match formula doesn't seem to be working

    =index($n$2:$n$43,match(t2,$o$2:$o$43,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

  4. #4
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2013
    Posts
    571

    Re: Index Match formula doesn't seem to be working

    Try this:

    Please Login or Register  to view this content.
    On Match, you need input the [match type].


    Regards
    SDCh
    Last edited by SDCh; 02-17-2013 at 07:59 PM.

  5. #5
    Registered User
    Join Date
    02-17-2013
    Location
    Portland, Oregon
    MS-Off Ver
    2010
    Posts
    2

    Re: Index Match formula doesn't seem to be working

    You just made my day. Thank you!!!

    David

+ 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