+ Reply to Thread
Results 1 to 7 of 7

Array formula not working

  1. #1
    bj
    Guest

    RE: Array formula not working

    just because a number is formated as a number doesn't mean it is one
    try
    {=INDEX('ALL DLV
    SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(value(trim(B84)),"*"&value(trim(mso))&"*"),0))}

    What are you trying to do with this equation? I haven't figured out the
    logic of it yet.

    "Alex" wrote:

    > I am using the following array formula which seems to only work for most of
    > the records. I don't use array formulas much and was wondering if there are
    > some special consideration I need to know when using. The records that
    > aren't working are on random rows. My mso range is correct. The only thing
    > that is different about some of the records that aren't working is that the
    > data in $E$4:$E$1999 is sometimes a #, but the cell is formatted as general.
    > I appreciate any ideas you might have.
    >
    > {=INDEX('ALL DLV SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(B84,"*"&mso&"*"),0))}


  2. #2
    Alex
    Guest

    RE: Array formula not working

    I'm doing the following:

    Sheet 1 has the following columns:

    MSO# ORDER
    NQCT8 111
    NQCT8 222
    NQFH4 333
    NQFJ6 444
    NQXF0 555

    Sheet 2 has the following columns:

    TRKCASE ORDER
    JVB65/NQXFO/Z1R09148 555
    JVB65/NQCT8/Z1K05629 222
    JVB65/NSTB4/Z1R09145
    JVB65/NTDZ9/84EX00053
    JVB65/NTZS1/Z1K05424

    I need a vlookup function in the ORDER column in Sheet 2 that will look to
    see if the TRKCASE in the same line matches any of the MSO#'s in Sheet1 and
    if so, enter the ORDER # from Sheet 1. What's confusing me is that the MSO#
    from Sheet 1 will be inside TRKCASE. I've included what ORDER #'s should end
    up in the ORDER column in Sheet 2.

    I tried your suggestion, substituting my cells, and was getting an error
    msg. Thanks

    "bj" wrote:

    > just because a number is formated as a number doesn't mean it is one
    > try
    > {=INDEX('ALL DLV
    > SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(value(trim(B84)),"*"&value(trim(mso))&"*"),0))}
    >
    > What are you trying to do with this equation? I haven't figured out the
    > logic of it yet.
    >
    > "Alex" wrote:
    >
    > > I am using the following array formula which seems to only work for most of
    > > the records. I don't use array formulas much and was wondering if there are
    > > some special consideration I need to know when using. The records that
    > > aren't working are on random rows. My mso range is correct. The only thing
    > > that is different about some of the records that aren't working is that the
    > > data in $E$4:$E$1999 is sometimes a #, but the cell is formatted as general.
    > > I appreciate any ideas you might have.
    > >
    > > {=INDEX('ALL DLV SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(B84,"*"&mso&"*"),0))}


  3. #3
    bj
    Guest

    RE: Array formula not working

    just because a number is formated as a number doesn't mean it is one
    try
    {=INDEX('ALL DLV
    SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(value(trim(B84)),"*"&value(trim(mso))&"*"),0))}

    What are you trying to do with this equation? I haven't figured out the
    logic of it yet.

    "Alex" wrote:

    > I am using the following array formula which seems to only work for most of
    > the records. I don't use array formulas much and was wondering if there are
    > some special consideration I need to know when using. The records that
    > aren't working are on random rows. My mso range is correct. The only thing
    > that is different about some of the records that aren't working is that the
    > data in $E$4:$E$1999 is sometimes a #, but the cell is formatted as general.
    > I appreciate any ideas you might have.
    >
    > {=INDEX('ALL DLV SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(B84,"*"&mso&"*"),0))}


  4. #4
    Alex
    Guest

    RE: Array formula not working

    I'm doing the following:

    Sheet 1 has the following columns:

    MSO# ORDER
    NQCT8 111
    NQCT8 222
    NQFH4 333
    NQFJ6 444
    NQXF0 555

    Sheet 2 has the following columns:

    TRKCASE ORDER
    JVB65/NQXFO/Z1R09148 555
    JVB65/NQCT8/Z1K05629 222
    JVB65/NSTB4/Z1R09145
    JVB65/NTDZ9/84EX00053
    JVB65/NTZS1/Z1K05424

    I need a vlookup function in the ORDER column in Sheet 2 that will look to
    see if the TRKCASE in the same line matches any of the MSO#'s in Sheet1 and
    if so, enter the ORDER # from Sheet 1. What's confusing me is that the MSO#
    from Sheet 1 will be inside TRKCASE. I've included what ORDER #'s should end
    up in the ORDER column in Sheet 2.

    I tried your suggestion, substituting my cells, and was getting an error
    msg. Thanks

    "bj" wrote:

    > just because a number is formated as a number doesn't mean it is one
    > try
    > {=INDEX('ALL DLV
    > SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(value(trim(B84)),"*"&value(trim(mso))&"*"),0))}
    >
    > What are you trying to do with this equation? I haven't figured out the
    > logic of it yet.
    >
    > "Alex" wrote:
    >
    > > I am using the following array formula which seems to only work for most of
    > > the records. I don't use array formulas much and was wondering if there are
    > > some special consideration I need to know when using. The records that
    > > aren't working are on random rows. My mso range is correct. The only thing
    > > that is different about some of the records that aren't working is that the
    > > data in $E$4:$E$1999 is sometimes a #, but the cell is formatted as general.
    > > I appreciate any ideas you might have.
    > >
    > > {=INDEX('ALL DLV SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(B84,"*"&mso&"*"),0))}


  5. #5
    Alex
    Guest

    Array formula not working

    I am using the following array formula which seems to only work for most of
    the records. I don't use array formulas much and was wondering if there are
    some special consideration I need to know when using. The records that
    aren't working are on random rows. My mso range is correct. The only thing
    that is different about some of the records that aren't working is that the
    data in $E$4:$E$1999 is sometimes a #, but the cell is formatted as general.
    I appreciate any ideas you might have.

    {=INDEX('ALL DLV SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(B84,"*"&mso&"*"),0))}

  6. #6
    bj
    Guest

    RE: Array formula not working

    just because a number is formated as a number doesn't mean it is one
    try
    {=INDEX('ALL DLV
    SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(value(trim(B84)),"*"&value(trim(mso))&"*"),0))}

    What are you trying to do with this equation? I haven't figured out the
    logic of it yet.

    "Alex" wrote:

    > I am using the following array formula which seems to only work for most of
    > the records. I don't use array formulas much and was wondering if there are
    > some special consideration I need to know when using. The records that
    > aren't working are on random rows. My mso range is correct. The only thing
    > that is different about some of the records that aren't working is that the
    > data in $E$4:$E$1999 is sometimes a #, but the cell is formatted as general.
    > I appreciate any ideas you might have.
    >
    > {=INDEX('ALL DLV SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(B84,"*"&mso&"*"),0))}


  7. #7
    Alex
    Guest

    RE: Array formula not working

    I'm doing the following:

    Sheet 1 has the following columns:

    MSO# ORDER
    NQCT8 111
    NQCT8 222
    NQFH4 333
    NQFJ6 444
    NQXF0 555

    Sheet 2 has the following columns:

    TRKCASE ORDER
    JVB65/NQXFO/Z1R09148 555
    JVB65/NQCT8/Z1K05629 222
    JVB65/NSTB4/Z1R09145
    JVB65/NTDZ9/84EX00053
    JVB65/NTZS1/Z1K05424

    I need a vlookup function in the ORDER column in Sheet 2 that will look to
    see if the TRKCASE in the same line matches any of the MSO#'s in Sheet1 and
    if so, enter the ORDER # from Sheet 1. What's confusing me is that the MSO#
    from Sheet 1 will be inside TRKCASE. I've included what ORDER #'s should end
    up in the ORDER column in Sheet 2.

    I tried your suggestion, substituting my cells, and was getting an error
    msg. Thanks

    "bj" wrote:

    > just because a number is formated as a number doesn't mean it is one
    > try
    > {=INDEX('ALL DLV
    > SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(value(trim(B84)),"*"&value(trim(mso))&"*"),0))}
    >
    > What are you trying to do with this equation? I haven't figured out the
    > logic of it yet.
    >
    > "Alex" wrote:
    >
    > > I am using the following array formula which seems to only work for most of
    > > the records. I don't use array formulas much and was wondering if there are
    > > some special consideration I need to know when using. The records that
    > > aren't working are on random rows. My mso range is correct. The only thing
    > > that is different about some of the records that aren't working is that the
    > > data in $E$4:$E$1999 is sometimes a #, but the cell is formatted as general.
    > > I appreciate any ideas you might have.
    > >
    > > {=INDEX('ALL DLV SHPMTS'!$E$4:$E$1999,MATCH(1,COUNTIF(B84,"*"&mso&"*"),0))}


+ 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