+ Reply to Thread
Results 1 to 5 of 5

Index Match Formula Error

  1. #1
    Registered User
    Join Date
    06-20-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2010
    Posts
    19

    Index Match Formula Error

    I have used an index match formula numerous times, and have never had a problem. For some reason it just won't work today. I have the following values in my sheet:

    Cal 08 60.00
    Cal 09 57.00
    Cal 10 61.00
    Cal 11 56.00
    Cal 12 44.00


    Look up value 60.00
    Result Cal 08


    When 60 is entered, Cal 08 is displayed as it should be.
    When 57 is entered, a #N/A error is displayed.
    When a number that is not in the list such as 99 is entered, Cal 12 is displayed

    I have attached this spreadsheet. Hopefully someone can explain to me why this isn't working.

    Index Match Formula Error.xlsx

    Thank you for any help you can provide!

    -B-

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Index Match Formula Error

    You have left off the last argument in your MATCH()...

    =INDEX(A$1:A$5,MATCH($B9,$B$1:$B$5,0))

    ,0) is for an exact match
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Index Match Formula Error

    What is your expected result when 99 is entered?
    Quote Originally Posted by DontExcelAtMuch View Post
    When a number that is not in the list such as 99 is entered, Cal 12 is displayed
    You need either
    =INDEX(A$1:A$5,MATCH($B9,$B$1:$B$5,0))
    This makes it an EXACT match, and will work for all entries that exist in A1:A5

    Or, leave the formula as is, and sort the data by column B ascending.
    This will be a closest match, and will make an entry of say 63, return the same value as if you entered 61.

  4. #4
    Registered User
    Join Date
    06-20-2013
    Location
    Calgary, Canada
    MS-Off Ver
    Excel 2010
    Posts
    19

    Re: Index Match Formula Error

    Thank you both for your help! A second opinion is all I needed to solve that little mistake.

  5. #5
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,938

    Re: Index Match Formula Error

    happy to help and thanks for 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. Replies: 3
    Last Post: 05-02-2013, 01:31 AM
  2. [SOLVED] ERROR in my INDEX MATCH Formula
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-14-2013, 10:19 AM
  3. [SOLVED] #REF! Error in INDEX & MATCH Formula!
    By Rajeshkumar R in forum Excel General
    Replies: 6
    Last Post: 06-09-2012, 09:00 AM
  4. I get an error in (Index & Match) Formula
    By MAHMUZ in forum Excel General
    Replies: 11
    Last Post: 11-14-2010, 05:41 AM
  5. If formula with index, match error
    By toclare84 in forum Excel General
    Replies: 2
    Last Post: 10-01-2010, 08:44 AM

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