+ Reply to Thread
Results 1 to 5 of 5

Index in array returning #NUM! error

  1. #1
    Registered User
    Join Date
    09-12-2010
    Location
    WI
    MS-Off Ver
    Excel 2003
    Posts
    6

    Index in array returning #NUM! error

    I am having trouble with an index formula that returns the #NUM! error. I have attached a sample of a workbook I am using. I have payment vouchers sheets (1001, 1002). The Data sheet collects information from all the voucher sheets. From there I have individual Cost Element sheets that should pick out their cost elements from the Data sheet and transfer the corresponding info to the sheet (date, check #, Vendor, amount, etc.) In the example attached cost sheet 724 is included.

    I am not very familiar with the Index formula and can't figure out what is wrong with this formula. I am hoping someone can.

    Thanks
    Attached Files Attached Files

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Index in array returning #NUM! error

    I think your problem is that D5 is a number....but your data column A is formatted as text, so you don't get a match. Try this version in A9

    =INDEX(Data!B$1:B$42,SMALL(IF(Data!$A$1:$A$42=$D5&"",ROW(Data!$A$1:$A$42)),ROW(1:1)))

    confirmed with CTRL+SHIFT+ENTER

    and copied across.

    The &"" converts D5 to a text value. Note that removing the dollar signs from in front of the column letters in Data!B$1:B$42 means that the column automatically increments as you copy across, no need to have 2,3,4 etc. at the end of the formula
    Audere est facere

  3. #3
    Registered User
    Join Date
    09-12-2010
    Location
    WI
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Index in array returning #NUM! error

    daddylonglegs:

    I am doing the dance of joy!! That was just what I needed. Can you help me with one more problem?? You knew that was coming. When I copy the formula down the page, the cells that do not find anything have an error message in them. I have added the ISERROR function but when I hit Ctrl, Shift, Enter I get a message: "You've entered too many arguments for this function." Any suggestions? Here is the formula I am trying to use:

    =IF(ISERROR(INDEX(Data!B$1:B$42,SMALL(IF(Data!$A$1:$A$42=$D$5&"",ROW(Data!$A$1:$A$42)),ROW(1:1))),"",INDEX(Data!B$1:B$42,SMALL(IF(Data!$A$1:$A$42=$D$5&"",ROW(Data!$A$1:$A$42)),ROW(1:1)))

    Thank you

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    21,528

    Re: Index in array returning #NUM! error

    Rather than always calculating the Array which is quite expensive you would be better storing the "Count" of rows to be returned elsewhere and referencing that.
    That way the Array is only calculated when necessary.

    Using your sample file:

    Please Login or Register  to view this content.
    Then

    Please Login or Register  to view this content.
    Then, adjust the Cumulative calculation:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-12-2010
    Location
    WI
    MS-Off Ver
    Excel 2003
    Posts
    6

    Re: Index in array returning #NUM! error

    DonkeyOte:

    Thank you so much. You solved all my problems!!!

+ 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