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))}

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(B8 4)),"*"&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))}

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(B8 4)),"*"&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))}

U cld do the same thing with a standard nested formula, of course,
assuming that the format of TRKCASE for the 1st 11 digits will always
be 5 digits "/" 5 digits.

=VLOOKUP(MID($B8,7,5),$E$4:$E$1999,2,FALSE)

This vlookup grabs the "NQFXO" in TRKCASE using the MID() function and
then searches for it in MSO# and then returns the corresponding order.

While testing out the sample data u provided I noticed that the last
MSO# had "NQFX - zero" whilst the TRKCASE had the zero as a the
alphabet O...

Cheers.


--
terabar
------------------------------------------------------------------------
terabar's Profile: http://www.excelforum.com/member.php...o&userid=24272
View this thread: http://www.excelforum.com/showthread...hreadid=379165