Re: Match Array returning #NA, Works individually

Discussion in 'Microsoft Excel Worksheet Functions' started by joeu2004, Aug 14, 2012.

  1. joeu2004

    joeu2004 Guest

    "KeriM" <> wrote:
    > I'm having trouble with a Match array function. They work
    > when I separate them into their own match function, but not
    > when I try to do an array. I'm trying to lookup two values
    > in different columns and return the corresponding row number.

    [....]
    > {MATCH(1,(('[OtherWB]Sheet1'!$B:$B=A3&"*")
    > * ('[OtherWB]Sheet1'!$C:$C="*"&RIGHT($A$4,3)&"*")),0)}


    It might help if you posted what you believe __does__ work. Also include
    some details about the contents of all the cells referenced.

    It would also help if you explained what you mean "does not work". Do you
    get an Excel error (which)? Does it return an incorrect number? [1]

    I suspect this never does what you intend, even in part, if you are thinking
    that the appended "*" will be interpreted as wildcard characters.

    But if the character "*" is actually in B:B and C:C (!), one question is:
    is OtherWB currently open in the same Excel instance?

    Otherwise, I see no reason why the array-entered formula above (as indicated
    by the curly braces)would fail as long there is a row in [OtherWB]Sheet1
    that contains both literally "abc*" in column B and literally "*xyz*" in
    column C, assuming that A3 contains "abc" and A4 contains "...xyz", where
    "..." is one or more characters.

    As an aside, it is "bad practice" to use ranges like B:B and C:C in this
    context.

    Arguably, it might not be so bad in Excel 2003 or earlier. But in Excel
    2007 and later, they cause Excel to create 2 arrays of 1+ million entries,
    to perform 1+ million arithmetic operations (multiply), and finally to
    process 1+ million cells when no match is found.


    ------
    [1] In addition to describing the problem better as suggested above, it
    would be best if you uploaded an example Excel file (devoid of any private
    data) that demonstrates the problem to a file-sharing website and posted the
    "shared", "public" or "view-only" link (aka URL; http://...) in a response
    here. The following is a list of some free file-sharing websites; or use
    your own.

    Box.Net: http://www.box.net/files
    Windows Live Skydrive: http://skydrive.live.com
    MediaFire: http://www.mediafire.com
    FileFactory: http://www.filefactory.com
    FileSavr: http://www.filesavr.com
    RapidShare: http://www.rapidshare.com
     
    joeu2004, Aug 14, 2012
    #1
    1. Advertisements

  2. joeu2004

    joeu2004 Guest

    "KeriM" <> wrote:
    > They do work on their own (The range is from my actual data):

    [....]
    > =Match(A3&"*",'[Data]Sheet1'!$B$1:$B$65536,0)
    > =Match("*"&RIGHT($A$4,3)&"*",'[Data]Sheet1'!$C$1:$C$65536,0)


    Try the following array-entered formula (press ctrl+shift+Enter instead of
    just Enter):

    =MATCH(1,(LEFT('[Data]Sheet1'!$B$1:$B$65536,LEN(A3))=A3)
    *(SEARCH(RIGHT($A$4,3),'[Data]Sheet1'!$C$1:$C$65536)<>0),0)

    "*" works as a wildcard character only in the first parameter of MATCH, not
    as part of an IF conditional expression.

    (But do you really expect to have up to 65K rows of data?! Rhetorical
    question.)


    "KeriM" <> wrote:
    > If you look at my attached sheets

    [....]
    > I can't upload to a file sharing site since I'm at work
    > and they are blocked and probably frowned upon.


    For future reference....

    I cannot see any "attached sheets" in my news reader.

    If you can post to a newsgroup and add attachments to postings (which is
    uploading, after all), there's a good chance that you can access one of the
    listed file-sharing websites and upload a file.

    Whatever you included as "attached sheets" (presumably an Excel file) could
    be uploaded as a file to a file-sharing website.

    You should be able to create a simple example Excel that demonstrates the
    problem, but that has no private information. I suspect that is exactly
    what you did for the included "attached sheets".
     
    joeu2004, Aug 15, 2012
    #2
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Guest

    individually time stamp a row when a change is made to that row

    Guest, Mar 27, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    0
    Views:
    175
    Guest
    Mar 27, 2006
  2. Guest

    meaning of : IF(Switch; Average(array A, array B); array A)

    Guest, Oct 24, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    359
    Bernard Liengme
    Oct 24, 2006
  3. Guest

    index match array function-returning only first match, need last.

    Guest, Dec 28, 2006, in forum: Microsoft Excel Worksheet Functions
    Replies:
    3
    Views:
    357
    Guest
    Dec 29, 2006
  4. jkfin1

    Match using array of column and row references to match with

    jkfin1, Sep 16, 2008, in forum: Microsoft Excel Worksheet Functions
    Replies:
    1
    Views:
    171
    Pete_UK
    Sep 16, 2008
  5. fatcatfan

    Complex conditional summing - array COUNT works, array SUM gives#VALUE

    fatcatfan, Nov 18, 2009, in forum: Microsoft Excel Worksheet Functions
    Replies:
    4
    Views:
    429
    T. Valko
    Nov 18, 2009
Loading...

Share This Page