+ Reply to Thread
Results 1 to 8 of 8

MATCH() & negative #s

  1. #1
    Forum Contributor
    Join Date
    02-20-2007
    Location
    Alaska
    MS-Off Ver
    2003, 2007, 2010
    Posts
    105

    MATCH() & negative #s

    I have a table that I am trying to find values from, and am using Index() & Match() together. All is well when the values are positive, but I get a #N/A if I look a negtative value.

    Match type is 1, and table is sorted negative to positive (both rows and columns).

    Here's the formula I am using:
    INDEX(Table!$B$4:$T$54, MATCH('Hit Log FY 07'!C6+1, Table!$A$4:$A$54,1),
    MATCH('Hit Log FY 07'!D6+250, Table!$B$3:$T$3,1))

    The rows of the table range from -55 to 55, and the columns range from
    -1000 to 10000.

    TIA!
    Last edited by F6Hawk; 02-20-2007 at 08:25 PM.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    The formula looks generally OK to me [apart from the ,""),"") bit at the end]

    You'll get #N/A if your values are lower than the lowest value, i.e. either lower than -55 for rows or -1000 for columns

  3. #3
    Forum Contributor
    Join Date
    02-20-2007
    Location
    Alaska
    MS-Off Ver
    2003, 2007, 2010
    Posts
    105
    Sorry about the ending parens, those are due to some blank checking going on (the formula actaully starts with an IF statement).

    As for the other part, I get a #N/A if I enter -1 or -500, too. IOW, there are values lower then the arguments being checked.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    As I say, the formula appears OK to me, unless the IF part is giving #N/A then I'm not sure what it could be. Can you attach your worksheet?

  5. #5
    Forum Contributor
    Join Date
    02-20-2007
    Location
    Alaska
    MS-Off Ver
    2003, 2007, 2010
    Posts
    105
    Here's the file. PLay with values in cells C6 & D6.
    Attached Files Attached Files

  6. #6
    Forum Contributor
    Join Date
    02-20-2007
    Location
    Alaska
    MS-Off Ver
    2003, 2007, 2010
    Posts
    105
    So can anyone see where the problem is?

    Thanks!

  7. #7
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675
    MATCH formulas sometimes don't like "filled down" values. I don't know if your table headers and row labels were created that way but I just manually re-input the values in A4:A54 and B3:T3 and it appears to work. here's a copy
    Attached Files Attached Files

  8. #8
    Forum Contributor
    Join Date
    02-20-2007
    Location
    Alaska
    MS-Off Ver
    2003, 2007, 2010
    Posts
    105
    Thanks, DDL, you helped me figger it out. I copied that table from another source, and after your post, I noticed that the - symbol in the file was actually longer than normal, more like _, except that it was in the middle of the cell. I replaced all of them with the proper - symbol, and all is well now.

    Thanks for the help!

    F6

+ Reply to 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1