+ Reply to Thread
Results 1 to 5 of 5

Index and Match combination producing the wrong result

  1. #1
    Registered User
    Join Date
    07-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    54

    Index and Match combination producing the wrong result

    Hi, I'm trying to get to grips with the index and match functions working in conjunction, as I need to find a result based on two criteria. I thought I'd written the code right, but its returning the wrong field?

    I've attached a sample file - the information in cell Q2 is wrong, and should display (as I'm guessing you can work out from the formula), the description that corresponds to an ISIN of GB0000566504 and a TD Quantity of -16034. If someone could tell me what I've been doing wrong, that would be wonderful.

    Thanks in advance.



    CFD Reconciliation File.xlsm

  2. #2
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,127

    Re: Index and Match combination producing the wrong result

    hi Authentik8, not quite sure if this is what you need:
    =INDEX(B94:B197,MATCH(1,(A94:A197=D2)*(D94:D197=H2),0))

    as you already did, use CTRL + SHIFT + ENTER. your array should be the same row number with the rest. 94:197

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  3. #3
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,128

    Re: Index and Match combination producing the wrong result

    Perhaps
    Please Login or Register  to view this content.
    committed with CSE ?
    Be very, very careful using IFERROR ! It hides ALL errors which is not always what you want to get correct results

  4. #4
    Registered User
    Join Date
    07-04-2012
    Location
    London, England
    MS-Off Ver
    Excel 2010
    Posts
    54

    Re: Index and Match combination producing the wrong result

    Thanks, for the quick reply, that's exactly what I needed! What was I doing wrong?

    Now my *only* challenge is to write a macro to make those arrays dependent on the data ranges... look out for my next post!

  5. #5
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,128

    Re: Index and Match combination producing the wrong result

    MATCH returns the position of an element in the specified range and INDEX references the cell in the same position in it's range ( in this case returns the value)
    If your ranges are not the same, the position being counted from the first cell of the INDEX array will not give you the correct result

+ 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