+ Reply to Thread
Results 1 to 3 of 3

NEED Help with the INDEX & MATCH in invoice tracking..!!

  1. #1
    Registered User
    Join Date
    04-25-2013
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    1

    NEED Help with the INDEX & MATCH in invoice tracking..!!

    Hi.

    My excel have 5 sheets: invoice, store, main, details, and sheet 5(for data).

    I would like to ask is this formula correct and it keep show out #NUM error..

    =INDEX(RDetails,SMALL(IF(RepairNoDetails=rngRepair,ROW(RDetails),ROW(RDetails[#Headers])),ROW(2:2)),MATCH(Repair!$B$4,RDetails[#Headers],0))


    Thank you!!
    Attached Files Attached Files
    Last edited by cein; 04-26-2013 at 12:17 AM. Reason: problem solved!

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,570

    Re: NEED Help with the INDEX & MATCH in invoice tracking..!!

    Possibly something like this...
    Attached Files Attached Files
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,181

    Re: NEED Help with the INDEX & MATCH in invoice tracking..!!

    Okay, first one of your defined names (RepairNoDetails) had the range in Quotes (="RDetails[Repair '#]" should be =RDetails[Repair '#])

    Modified your formula in Repair!B5 as follows
    WAS
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    =IFERROR(INDEX(RDetails,SMALL(IF(RepairNoDetails=rngRepair,ROW(RDetails)-ROW(RDetails[#Headers])),ROW(1:1)),MATCH(Repair!B$4,RDetails[#Headers],0)),"-")
    It also needs to be entered as an array function. Enter with CNTRL SHFT ENTER instead of ENTER. If done properly, you'll see {} around your formula.
    See attachment
    Attached Files Attached Files
    ChemistB
    My 2

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

+ 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