Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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))} |
#2
|
|||
|
|||
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))} |
#3
|
|||
|
|||
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))} |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel2000 A working formula has {braces} which disappear. Why? | Excel Worksheet Functions | |||
Formula entered not working | Excel Discussion (Misc queries) | |||
Array Formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) | |||
Help with array formula | Excel Worksheet Functions |