+ Reply to Thread
Results 1 to 4 of 4

INDEX function not working

  1. #1
    Registered User
    Join Date
    08-16-2012
    Location
    Attignat, France
    MS-Off Ver
    Excel 2007
    Posts
    4

    INDEX function not working

    I seriously need help here. Attached is a copy of my document and I have used the following formula:

    =IFERROR(INDEX(Data!$F$2:$F$5839,MATCH(1,(Sheet3!A16=Data!A2:A5839)*(Sheet3!D1=Data!H2:H5839)*(Sheet3!L2=Data!K2:K5839),0)),"")

    It is supposed to return "A*" but instead, there is no value. Due to the iferror() function, it has shown up as blank in cell L16 in Sheet3.

    I have checked the format of the cells as well as tried different functions such as vlookup(), sumproduct(), etc. but nothing worked.

    Can you help?
    Attached Files Attached Files

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: INDEX function not working

    You just have to commit the formula with Ctrl+Shift+Enter, it's an array formula

  3. #3
    Registered User
    Join Date
    08-16-2012
    Location
    Attignat, France
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: INDEX function not working

    Thank you. That worked! Why do I need to press ctrl-shift-enter for this? As for the other formulas, we don't really need to do that.

  4. #4
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,446

    Re: INDEX function not working

    It is because the range part of the MATCH function has to create an array with (Sheet3!A16=Data!A2:A5839)*(Sheet3!D1=Data!H2:H5839)*(Sheet3!L2=Data!K2:K5839).
    Some functions, like SUMPRODUCT are array functions, but MATCH is not, so you have to tell XL to use it as an array formula

    You can find explanations here. Also Googling around will give you links

+ 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. Correct/Working (Index,Match) formula not working between cells
    By barnerd in forum Excel Formulas & Functions
    Replies: 17
    Last Post: 02-11-2014, 01:20 PM
  2. Index and Match function not working
    By Ystar in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-02-2013, 07:11 AM
  3. [SOLVED] Index/Match Function not working
    By melnemac32 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-14-2013, 07:11 AM
  4. [SOLVED] ISNA with INDEX(MATCH) function not working
    By wyndland in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 02-22-2013, 11:37 PM
  5. Index-Match Function Not Working all the time
    By hoosierhunter in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 12-08-2007, 06:58 PM

Tags for this Thread

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