+ Reply to Thread
Results 1 to 5 of 5

Index/match giving correct results, wrong results & #N/A results...sometimes

  1. #1
    Forum Contributor
    Join Date
    05-22-2012
    Location
    Boise, Idaho
    MS-Off Ver
    Office 365
    Posts
    156

    Index/match giving correct results, wrong results & #N/A results...sometimes

    I have an index match formula that is sometime giving correct data, sometime incorrect data and sometime #N/A.

    I have the complete list of clients in column A. I want column B to provide the assigned Unit # and then column C to give the unit leader. Column D I have listed some notes as to what Column B & C should state.


    Can you help me fix the formulas in column B and C? Document attached
    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,443

    Re: Index/match giving correct results, wrong results & #N/A results...sometimes

    Use the Exact match parameter =INDEX($F$2:$F$96,MATCH(A5,$E$2:$E$96,0))

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,029

    Re: Index/match giving correct results, wrong results & #N/A results...sometimes

    You're formula in B2 should be
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Although you will still have problems as the values in col A don't always match the values in col E

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Index/match giving correct results, wrong results & #N/A results...sometimes

    The default for MATCH is to assume that your data is sorted in ascending order, and it will find the first match that is less than or equal to the lookup value. Your data is not sorted, so you need explicitly specify the third argument to indicate that you want an exact match for unsorted data:

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  5. #5
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: Index/match giving correct results, wrong results & #N/A results...sometimes

    Three for the price of one!

+ 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. [SOLVED] Index Match across two sheets giving unexpected results
    By Sprong in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-30-2019, 04:02 PM
  2. Index Match Formula Not Pulling Correct Results
    By derivative2016 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 06-23-2016, 12:54 PM
  3. Index Match Formula Not Pulling Correct Results
    By derivative2016 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-23-2016, 10:33 AM
  4. Replies: 8
    Last Post: 06-07-2016, 11:28 AM
  5. [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
  6. [SOLVED] Rank countif with index match is not giving top results
    By amartin575 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-01-2014, 10:17 PM
  7. [SOLVED] Using Index Match or Vlookup giving unexpected results on imported data
    By jacob@thepenpoint in forum Excel General
    Replies: 2
    Last Post: 07-03-2012, 05:49 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1