+ Reply to Thread
Results 1 to 6 of 6

Problem with a simple Index Match formula returning inconsistent results

  1. 04-20-2017, 06:16 AM #1
    SueBristow
    SueBristow is offline
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Problem with a simple Index Match formula returning inconsistent results

    Hi

    I am trying to use Index Match to return a value but I am getting inconsistent errors.

    I have taken out the parts of the spreadsheet so that I can upload it here. I am trying to find a student's forename and surname from their admission number. Most results are correct but I have 4 on this sheet that are returning incorrect results and I have no idea why. I have filled the cells in yellow of the offending results.

    It appears to be when the admission number goes out of sync but I thought that this would be the point of something like this. I have over the last few days built a few spreadsheets using INDEX and MATCH and not noticed an issue so I am now worried that I have mis-understood the syntax of the formula and that all of the spreadsheets that I have created using this formula have issues.

    Thank you
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 04-20-2017, 06:19 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Problem with a simple Index Match formula returning inconsistent results

    Tiny omission:

    =INDEX(B$3:B$32,MATCH(E3,A$3:A$32),0)

    should be

    =INDEX(B$3:B$32,MATCH(E3,A$3:A$32,0),0)
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 04-20-2017, 06:24 AM #3
    SueBristow
    SueBristow is offline
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Re: Problem with a simple Index Match formula returning inconsistent results

    Can I ask what that other 0 does please? It works perfectly BTW.

    Thank you
    Reply With QuoteRegister To Reply
  4. 04-20-2017, 06:30 AM #4
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Problem with a simple Index Match formula returning inconsistent results

    Yes, of course - it forces the MATCH to be exact. 1 would force an approximate match, which is what it seems to default to if you forget that parameter.
    Reply With QuoteRegister To Reply
  5. 04-20-2017, 06:31 AM #5
    SueBristow
    SueBristow is offline
    Registered User
    Join Date
    04-20-2017
    Location
    Christchurch, Dorset
    MS-Off Ver
    2016
    Posts
    29

    Re: Problem with a simple Index Match formula returning inconsistent results

    Thank you - that makes sense
    Reply With QuoteRegister To Reply
  6. 04-20-2017, 06:44 AM #6
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,780

    Re: Problem with a simple Index Match formula returning inconsistent results

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Index Match formula not returning correct results
    By JennOlsen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 10-30-2015, 02:59 PM
  2. Index Match Returning Results for Filtered Out Data
    By hermes980 in forum Excel General
    Replies: 2
    Last Post: 08-27-2014, 12:19 AM
  3. Index/Match or VLookup returning Multiple Results
    By Stoppeh in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-21-2014, 08:31 PM
  4. [SOLVED] index match not returning all results
    By nellyc in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 12-06-2013, 11:22 AM
  5. index match not returning any results
    By nellyc in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-03-2013, 03:29 PM
  6. [SOLVED] index match not returning all results
    By nellyc in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-25-2013, 08:06 AM
  7. If/VLookup/Match Formula Results Inconsistent
    By hardpenguin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-08-2010, 05:05 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1