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