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

1. ## 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.  Register To Reply

2. ## 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  Register To Reply

3. ## 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?  Register To Reply

4. ## 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.  Register To Reply

5. ## 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)...  Register To Reply

6. ## 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.  Register To Reply

7. ## 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   Register To Reply