Match Array returning #NA, Works individually

[KeriM]

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

This is my function:


Code
-------------------



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



-------------------


Any help is appreciated. Thanks

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

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

 

[KeriM]

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. Als
include
some details about the contents of all the cells referenced.

It would also help if you explained what you mean "does not work". D
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 ar
thinking
that the appended "*" will be interpreted as wildcard characters.

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

Otherwise, I see no reason why the array-entered formula above (a
indicated
by the curly braces)would fail as long there is a row in [OtherWB]Sheet

that contains both literally "abc*" in column B and literally "*xyz*" i

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 thi

context.

Arguably, it might not be so bad in Excel 2003 or earlier. But in Exce

2007 and later, they cause Excel to create 2 arrays of 1+ millio
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, i

would be best if you uploaded an example Excel file (devoid of an
private
data) that demonstrates the problem to a file-sharing website and poste
the
"shared", "public" or "view-only" link (aka URL; http://...) in
response
here. The following is a list of some free file-sharing websites; o
use
your own.

Box.Net: http://www.box.net/files
Windows Live Skydrive: http://skydrive.live.com
MediaFi http://www.mediafire.com
FileFactory: http://www.filefactory.com
FileSavr: http://www.filesavr.com
RapidSha http://www.rapidshare.com

They do work on their own (The range is from my actual data):


Code
-------------------


=Match(A3&"*",'[Data]Sheet1'!$B$1:$B$65536,0)

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


-------------------


I can't get them to work together to provide me with where they match o
the same row. If you look at my attached sheets (this is how it works o
my real data, not sure about the dummy data), the first one would retur
animal in B2 and the second one will return james in C3. I need it t
return both in row C3, since this is where my data is.

Since I have various characters before/after the strings I'm searchin
for, it wasn't picking my string up without the "*". Is there anothe
way to provide a wildcard function?

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

I understand if this limitation prevents you all from helping me, it wa
worth a shot to ask

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[joeu2004]

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

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".

 

[KeriM]

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 instea
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.)

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 i

uploading, after all), there's a good chance that you can access one o
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 demonstrate
the
problem, but that has no private information. I suspect that is exactl

what you did for the included "attached sheets".

That gave me a #Value error as well...I was able to do what I needed t
via VBA code, so I don't need to use a formula anymore. Thanks for you
help though

+-------------------------------------------------------------------
+-------------------------------------------------------------------