Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Match Array returning #NA, Works individually
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.
This is my function: Code:
{MATCH(1,(('[OtherWB]Sheet1'!$B:$B=A3&"*") * ('[OtherWB]Sheet1'!$C:$C="*"&RIGHT($A$4,3)&"*")),0)} |
#2
|
|||
|
|||
Quote:
Hi Keri, Could you please help us to help you by providing an example workbook? You know what your data looks like, how it should behave and what results you're after, but we have nothing more than your words above so are a little blind in the grand scheme of things, which makes things a little hard to fathom sometimes. |
#3
|
|||
|
|||
Quote:
I uploaded two sheets. Let me know if you have any problems. They're dummy sheets, but you can see the layout of my data. On the Check Sheet, I want to reference the Data sheet and find the row where it says "Animal" (cell A3) and "James" (cell A4) and write the subtracted Numerator and Denominator amount in the "Number Here" cell. The data itself makes no sense, but hopefully you can get the gist of what I'm after. Thanks again! |
#4
|
|||
|
|||
Quote:
So what should the value in cell C4 be? I presume you also need on in C5 and so on? What should they be. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Array returning #NA, Works individually
"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 MediaFi http://www.mediafire.com FileFactory: http://www.filefactory.com FileSavr: http://www.filesavr.com RapidSha http://www.rapidshare.com |
#6
|
|||
|
|||
Quote:
Similarily, for C5, I want to find "Animal" and "John" in the data sheet which would result in the value of 20 (50-30) in cell C5. The site said I couldn't directly upload an excel sheet. Is that not the case? I can't upload this on a file sharing site since I'm doing this on a work computer and all uploading sites are blocked. |
#7
|
|||
|
|||
Quote:
Code:
=Match(A3&"*",'[Data]Sheet1'!$B$1:$B$65536,0) =Match("*"&RIGHT($A$4,3)&"*",'[Data]Sheet1'!$C$1:$C$65536,0) Since I have various characters before/after the strings I'm searching for, it wasn't picking my string up without the "*". Is there another way to provide a wildcard function? I can't upload to a file sharing site since I'm at work and they are blocked and probably frowned upon. I understand if this limitation prevents you all from helping me, it was worth a shot to ask. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match Array returning #NA, Works individually
"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". |
#9
|
|||
|
|||
Quote:
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
Match returning #N/A | Excel Worksheet Functions | |||
Returning an array from a multi-dimensional array | Excel Programming | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
Match font color works ... but | Excel Programming |