+ Reply to Thread
Results 1 to 11 of 11

Index Match Problem

  1. #1
    Registered User
    Join Date
    04-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    38

    Index Match Problem

    I've recently learned how to use the INDEX MATCH function but have a problem. I've put together a lookup using INDEX MATCH that works fine in most workbooks but won't work in a workbook provided by a Local Authority unless I copy and paste the text from the workbook where I keep the original INDEX MATCH example into the Local Authority workbook.

    I've tried using format painter to try and match the formatting of the 2 workbooks but to no avail....HELP!!

  2. #2
    Valued Forum Contributor Lemice's Avatar
    Join Date
    04-13-2013
    Location
    Somewhere.
    MS-Off Ver
    Excel 2016
    Posts
    696

    Re: Index Match Problem

    Hello,

    Can you attach the sample file in which your formula does not work? It will be easier for other to help you.
    (copy pasta from Ford)
    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

    Regards,
    Lem

  3. #3
    Registered User
    Join Date
    04-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    38

    Re: Index Match Problem

    I'll do it tomorrow Lemice as I have to hit the sack for work in the morning!

    Thanks for the prompt reply

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,059

    Re: Index Match Problem

    Just FYI, formatting just changes how data looks, it does nothing to change the actual data itself.

    Chech for leading/trailing spaces in the data you are searching. If you can find 2 sets of data that should match - but dont, use =exact(cell1,cell2) to see if they are in fact the same
    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

  5. #5
    Registered User
    Join Date
    04-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    38

    Re: Index Match Problem

    Problem Worksheet Index Match.xlsx

    FDibbins

    I've tried removing leading spaces with a macro I have but to no avail; also used the =exact function to compare cells that were recognised by the Index Match and those that were not...got a "TRUE" answer.

    Lemice

    I've created an example of the problem I'm having by pasting in cells from the problem worksheet into the attached spreadsheet. As you can see, the first row works because I've cut and pasted data from the original data table into this row. The rows showing #N/A are from the problem worksheet.

  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 Problem

    YOu have trailing spaces in column Q
    In row4 (the one that works) there IS a trailing space on D4
    In row5 and beyond, there is NOT a trailing space in D

    Try this in F4
    =INDEX($N$4:$R$63,MATCH(TRIM(A4&B4&C4&D4),TRIM($N$4:$N$63&$O$4:$O$63&$P$4:$P$63&$Q$4:$Q$63),0),5)

  7. #7
    Registered User
    Join Date
    04-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    38

    Re: Index Match Problem

    Cheers Jonmo1; I've identified a problem in column D...if I change this one cell to the corresponding cell that does work the formula works. I'm going to try your formula and if it works I'm going to source a macro to remove trailing spaces.

  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 Problem

    Try using Data - Text To Columns - Deliminated - Space - Finish.
    On both columns from Row4 down

  9. #9
    Registered User
    Join Date
    04-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    38

    Re: Index Match Problem

    Yes!!!

    Found a trailing / leading spaces removing code that worked at the following link: https://groups.google.com/forum/?fro...sc/Lzv_sp0_-xM

    Please Login or Register  to view this content.
    Next
    Application.Calculation = xlAutomatic
    End Sub[/CODE]Thanks guys, particularly Jonmo1 for taking a look at this for me so quickly; really appreciated.
    Last edited by Limitedtimeonearth; 04-19-2013 at 03:46 PM.

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

    Re: Index Match Problem

    Great, glad to help out.

  11. #11
    Registered User
    Join Date
    04-17-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    38

    Unhappy Re: Still Have A Problem!!

    Format Problem.xlsx

    I'm still having problems with the worksheet; I've run the macro to remove the trailing and leading spaces but the INDEX MATCH function is again not working in the cells highlighted in red.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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