+ Reply to Thread
Results 1 to 8 of 8

Index, Match, CountIf Formula returning inocrrect data

  1. #1
    Registered User
    Join Date
    03-11-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    4

    Index, Match, CountIf Formula returning inocrrect data

    Hi all

    {=INDEX(files!$A:$A,MATCH(1,COUNTIF($D40,"*"&files!$A:$A&"*"),0))}

    I am using the formula above to extract a file number from a string of data where I never know where the file number will be (if it is there at all)

    It works to a point however, due to the wildcards in the formula if my files list has W250/4 followed by W250/459 it will return W250/4 when the exact match is W250/459.

    I think reversing the sort order of the files data Z-A rather than A-Z may fix this, but this would mean re-sorting data each time.

    I was going to record a macro to resort on opening file, but macros are wiped from our servers overnight

    If this is my only option then I will manually resort, but would love a way around this if possible!

    Thanks all for any help - hope I have given enough details (new to this)

    Jo

  2. #2
    Forum Expert
    Join Date
    09-01-2012
    Location
    Norway
    MS-Off Ver
    Office 365
    Posts
    2,572

    Re: Index, Match, CountIf Formula returning inocrrect data

    If you post a file with sample data it will be much easier to help you.
    <----- If you were helped by my posts you can say "Thank you" by clicking the star symbol down to the left

    If the problem is solved, finish of the thread by clicking SOLVED under Thread Tools
    I don't wish to leave you with no answer, yet I sometimes miss posts. If you feel I forgot you, remind me with a PM or just bump the thread.

  3. #3
    Registered User
    Join Date
    03-11-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Index, Match, CountIf Formula returning inocrrect data

    Thanks.

    I attach a file and i have highlighted the section showing incorrect file ref (P603/2 rather than P603/25)

    The files list on the second tab

    Thanks for heads up
    Attached Files Attached Files

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

    Re: Index, Match, CountIf Formula returning inocrrect data

    +1, a sample book would be very helpful.

    Will the file number within D40 be seperated by spaces ?

    "some text this is great file number more text here."

    If so, try
    =INDEX(files!$A:$A,MATCH(1,COUNTIF($D40,"* "&files!$A:$A&" *"),0))

  5. #5
    Registered User
    Join Date
    03-11-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Index, Match, CountIf Formula returning inocrrect data

    Thanks Jonmo1

    There is no way to predict where the file number will be: it could be at the beginning, middle or end, and there may or may not be a space after it - this field is filled in by users who NEVER follow instructions on format...

    I have posted a made up example sheet which I hope helps

    Thanks for your time

    Jo

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

    Re: Index, Match, CountIf Formula returning inocrrect data

    This is the root of all evil.
    Quote Originally Posted by jj190870 View Post
    this field is filled in by users who NEVER follow instructions on format...
    I think you already mentioned the best solution, to sort the file numbers in files!A:A in DEscending order.

    Other than that, your efforts will be better spent educating the users on correct data entry.
    Rather than racking your brain trying to make formulas that accomodate their laziness.
    Once they know you'll do that, it will only get worse.

  7. #7
    Registered User
    Join Date
    03-11-2015
    Location
    London, England
    MS-Off Ver
    2010
    Posts
    4

    Re: Index, Match, CountIf Formula returning inocrrect data

    Thanks for confirming

    I thought that might be what I would have to do, but thought I'd 'ask the experts' first

    Thanks for you time

    Jo

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

    Re: Index, Match, CountIf Formula returning inocrrect data

    You're welcome.

+ 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. Index Match Returning Results for Filtered Out Data
    By hermes980 in forum Excel General
    Replies: 2
    Last Post: 08-27-2014, 12:19 AM
  2. [SOLVED] Indirect-index-match formula is returning #Value
    By brent_excel in forum Excel General
    Replies: 3
    Last Post: 08-01-2014, 05:52 PM
  3. Issues with Index Match formula returning values and some #ref
    By kokapelly in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-27-2014, 04:47 PM
  4. [SOLVED] IFERROR+INDEX+MATCH Formula is not returning the correct value
    By bxk006 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 08-16-2013, 09:50 AM
  5. Index Max Match formula returning wrong value
    By bk77 in forum Excel General
    Replies: 4
    Last Post: 03-25-2009, 02:17 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