Index Match Array inconsistent data
Results 1 to 5 of 5

Thread: Index Match Array inconsistent data

  1. #1
    New Member
    Join Date
    Sep 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Index Match Array inconsistent data

    Hello
    I am and have been a huge fan of Index Match formula but never use it in an array to have multiple lookup values, until today. The formula is inconsistently working.

    I need the formula entered in 1300 rows. When I enter the formula in the first cell, it works. When I copy down, it works in some cells and doesn't in others. What is interesting to me is that the cells where it is not working, have the same lookup values as the cells where it did work. What am I missing? I even go into a cell where it did not work and perform the CSE and it still returns #N/A.



    Array Formula: =INDEX([Spreadsheet Name]TABNAME!$D:$D,MATCH(1,([Spreadsheet Name]TABNAME!$C:$C=AF11)*([Spreadsheet Name]TABNAME!$E:$E=AH11),0)) and then CSE

    AF11 - is the start of the rows of data
    "SPREADSHEET NAME" = I have a spreadsheet with 6 columns - Column C = a 3-letter code; Column D = The description of that code; Column E = a 1-letter code; Column F = The Description of that code


    The spreadsheet with the formula has the codes and I need to bring over the descriptions.
    A 3-letter code will be listed more than once with a different 1-letter code and descriptions will change based on the combinations, which is why I needed multiple lookup values.

    When I copy down - formula works until row 52. Rows 52 - 837 = #N/A ; Rows 838 - 865 = displays data ; Rows 866 - 1310 = #N/A

    Rows 51 and 52 have the same values in AF and AH. Why do you think the formula is inconsistent? What am I missing?


    Thank you!!!
    -Elle_H

  2. #2
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match Array inconsistent data

    Quote Originally Posted by Elle_H View Post

    Rows 51 and 52 have the same values in AF and AH. Why do you think the formula is inconsistent? What am I missing?
    Check if really AF51=AF52 and if AH51=AH52.
    Try in empty cells
    =AF51=AF52
    =AH51=AH52

    Both should return TRUE, otherwise i suspect there are extraneous characters (spaces?) in your data.

    M.

  3. #3
    New Member
    Join Date
    Sep 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match Array inconsistent data

    Also, when I free type the formula (and CSE) in a cell where it did not pull data after copy down (#N/A), it still brings back #N/A. But I have other rows with the same exact values in AF and AH, where the formula did work.

  4. #4
    New Member
    Join Date
    Sep 2015
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Index Match Array inconsistent data

    Well I'll be. I don't even know how spaces could have gotten here. wtf. wow. That worked. M - you are AMAZING. Thank you thank you thank you!!!!! -Elle_H
    Last edited by Elle_H; Jul 18th, 2019 at 04:53 PM.

  5. #5
    MrExcel MVP
    Join Date
    Aug 2010
    Location
    Rio de Janeiro BRAZIL
    Posts
    16,228
    Post Thanks / Like
    Mentioned
    19 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Index Match Array inconsistent data

    You are welcome. Thanks for the feedback.

    To clean up your data take a look at
    http://dmcritchie.mvps.org/excel/join.htm#trimall

    M.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •