+ Reply to Thread
Results 1 to 5 of 5

IFERROR+INDEX+MATCH Formula is not returning the correct value

  1. #1
    Registered User
    Join Date
    06-21-2013
    Location
    west allis
    MS-Off Ver
    Excel 2007
    Posts
    28

    IFERROR+INDEX+MATCH Formula is not returning the correct value

    On the second tab of the spreadsheet, i am utilizing an iferror+index+match formula to return net assets from the first tab. It should search for the fund and date, then pull the corresponding assets. This formula has worked on other spreadsheets, so i am not sure if there is an option i need to check or what is wrong. But it works well when you have multiple dates and multiple fund names in which you need to pull. Help would be greatly appreciated.
    Attached Files Attached Files

  2. #2
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IFERROR+INDEX+MATCH Formula is not returning the correct value

    You have to enter those formulas as array formulas.

    Array formulas are entered differently than a regular formula. After you type in a regular formula you hit the ENTER key. With an array formula you *must* use a combination of keys.Those keys are the CTRL key, the SHIFT key and the ENTER key. That is, hold down both the CTRL key and the SHIFT key then hit the ENTER key.

    When done properly Excel will enclose the formula in squiggly brackets { }. You can't just type these brackets in, you *must* use the key combo to produce them. Also, anytime you edit an array formula it *must* be re-entered as an array using the key combo.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: IFERROR+INDEX+MATCH Formula is not returning the correct value

    Those are array formulas... you need to confirm\ them with CTRL+SHIFT+ENTER not just ENTER so that you see { } brackets appear around the formula.

    An alternative so that you can use ENTER only, is this:

    =IFERROR(INDEX(NetAssets,MATCH(1,INDEX((B$2=FundName)*($A4=Date),0),0)),0)

    and
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  4. #4
    Registered User
    Join Date
    06-21-2013
    Location
    west allis
    MS-Off Ver
    Excel 2007
    Posts
    28

    Re: IFERROR+INDEX+MATCH Formula is not returning the correct value

    Thanks all. I knew i was missing something simple.

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: IFERROR+INDEX+MATCH Formula is not returning the correct value

    You're welcome. We appreciate the feedback!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Wrong result displaying using IFERROR-INDEX-MATCH-ROW Formula
    By paradise2sr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-16-2013, 01:03 PM
  2. comparision between two excel sheet using VBA Code for Index/match/and/iferror formula
    By gaurangaero in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-28-2012, 10:08 AM
  3. [SOLVED] Need help to correct IFERROR AND INDEX Formula
    By ksathwal in forum Excel - New Users/Basics
    Replies: 5
    Last Post: 11-12-2012, 09:57 PM
  4. Looking for better formula of iferror, index and match
    By DavidRoger in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-23-2012, 07:50 AM
  5. Excel 2007 : iferror(index Formula returning blank cells
    By Martin Chamberlin in forum Excel General
    Replies: 7
    Last Post: 11-15-2011, 08:45 AM

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