+ Reply to Thread
Results 1 to 7 of 7

Converting 3x10 array to a 1X30 array to run a Match formula

  1. #1
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Converting 3x10 array to a 1X30 array to run a Match formula

    Say I have 3 columns of data: A1:C10 and I want to run a Match() function on them all together to see if I get a match any one those cells, say the value of have in X1.

    Since, Match only allows a One-Column lookup array.. is there a way to "concatenate" or "append" the 3 columns together within a formula so now I would be looking to Match in an array that is 1 column * 30 rows?

    Basically want to convert =Match(X1,A1:C10,0) to =Match(X1,A1:A30,0) without moving around the raw data in the sheet.

    And I want to avoid doing an AND or OR formula that uses 3 separate MATCH() for each column.

    I have a hunch that the MMULT or MMULT/TRANSPOSE functions are involved, but can't seem to get it right.

    Thanks.
    Last edited by NBVC; 04-08-2009 at 04:34 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  2. #2
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: Converting 3x10 array to a 1X30 array to run a Match formula

    presumably the below isn't allowed ?

    =MIN(IF(ISNUMBER(MATCH(A1:C10,X1,0)),ROW(A1:A10)))
    CSE

  3. #3
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Converting 3x10 array to a 1X30 array to run a Match formula

    To tell you the truth I was trying to assist another user here: http://www.excelforum.com/excel-misc...-of-cells.html

    and I had suggested a working formula as below:
    Please Login or Register  to view this content.
    (which turns out isn't what they were looking for.. but got me thinking....)

    I want to be able to shrink it to:
    Please Login or Register  to view this content.
    but I can't use the Red range... how could I consolidate the range to a single array?
    Last edited by NBVC; 04-07-2009 at 02:26 PM.

  4. #4
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Converting 3x10 array to a 1X30 array to run a Match formula

    Assuming that A16:C25 contains numerical values, the following will convert the 3X10 array into a 1X30 array...

    Please Login or Register  to view this content.
    However, here's an alternative that uses a helper column...

    Please Login or Register  to view this content.
    The formula will return a 1 if the value in Column F occurs in A16:C25, otherwise it returns 0. Then base the formula to return the desired result on Column G.

  5. #5
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Converting 3x10 array to a 1X30 array to run a Match formula

    oooh... a little more complex than I expected.... (which, I guess makes me feel good in a way, thinking it was easier and I just wasn't getting it.)

    I applied the first formula like so:

    Please Login or Register  to view this content.
    but didn't get expected result..

    I.e. I put a 5 in F16 and had an occurance of a 5 in A16:C25, but it still returned "non existing". Did I apply it correctly?

    Also, the data may have values that could be a mix of text/numbers too.

    Thanks Domenic.

    (Note: I am logging off now and will check back later or tomorrow)...

  6. #6
    Forum Expert
    Join Date
    06-18-2004
    Location
    Canada
    MS-Off Ver
    Office 2016
    Posts
    1,474

    Re: Converting 3x10 array to a 1X30 array to run a Match formula

    Try...

    =IF(SUM(IF(ISNA(MATCH("*"&F16:F25&"*",A16:A25&"",0))+ISNA(MATCH("*"&F16:F25&"*",B16:B25&"",0))+ISNA(MATCH("*"&F16:F25&"*",C16:C25&"",0))=3,1)),"Non-existing","Existing")

    ...confirmed with CONTROL+SHIFT+ENTER. By the way, if you're actually trying to match exact values, as your previous post suggests, the following would suffice...

    =If(SUMPRODUCT(--(COUNTIF(A16:C25,F16:F25)=0)),"Non-existing","Existing")

    In case you're curious about the previous method, here's how it would be done...

    First define the following...

    Array1:

    =INT((ROW(INDIRECT("1:"&ROWS($A$16:$C$25)*COLUMNS($A$16:$C$25)))-1)/3)

    Array2:

    =MOD((ROW(INDIRECT("1:"&ROWS($A$16:$C$25)*COLUMNS($A$16:$C$25)))-1),3)

    Array3:

    =N(OFFSET($A$16:$C$25,Array1,Array2,1,1))

    Then try...

    =IF(SUM(IF(F16:F25<>"",IF(ISNA(MATCH("*"&F16:F25&"*",Array3&"",0)),1))),"Non-existing","Existing")

    ...confirmed with CONTROL+SHIFT+ENTER. If, however, the data contains both text and numerical values, the process of converting to a 1X30 array becomes much more expensive.
    Last edited by Domenic; 04-08-2009 at 07:11 AM.

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Converting 3x10 array to a 1X30 array to run a Match formula

    Try...

    =IF(SUM(IF(ISNA(MATCH("*"&F16:F25&"*",A16:A25&"",0))+ISNA(MATCH("*"&F16:F25&"*",B16:B25&"",0))+ISNA(MATCH("*"&F16:F25&"*",C16:C25&"",0))=3,1)),"Non-existing","Existing")

    ...confirmed with CONTROL+SHIFT+ENTER.
    Hi Domenic, that is pretty much the way I worked around it now... I was trying to consolidate the 3 Match() functions... I thought there could be some clever way to use MMULT

    By the way, if you're actually trying to match exact values, as your previous post suggests, the following would suffice...

    =If(SUMPRODUCT(--(COUNTIF(A16:C25,F16:F25)=0)),"Non-existing","Existing")
    Thanks for this... I am, however, not looking for exact matches, hence the wildcarding in my original formula

    In case you're curious about the previous method, here's how it would be done...

    First define the following...

    Array1:

    =INT((ROW(INDIRECT("1:"&ROWS($A$16:$C$25)*COLUMNS($A$16:$C$25)))-1)/3)

    Array2:

    =MOD((ROW(INDIRECT("1:"&ROWS($A$16:$C$25)*COLUMNS($A$16:$C$25)))-1),3)

    Array3:

    =N(OFFSET($A$16:$C$25,Array1,Array2,1,1))

    Then try...

    =IF(SUM(IF(F16:F25<>"",IF(ISNA(MATCH("*"&F16:F25&"*",Array3&"",0)),1))),"Non-existing","Existing")

    ...confirmed with CONTROL+SHIFT+ENTER. If, however, the data contains both text and numerical values, the process of converting to a 1X30 array becomes much more expensive.
    Thanks again for this Domenic... I will keep this in my library
    Last edited by NBVC; 04-08-2009 at 07:49 AM.

+ 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