AMATCHES2 can only accept multiple columns if consecutive (and numbering 5 or less)

[John Brown]

Hello, I think AMATCHES2 can only accept multiple columns if consecutive. eg this works:

=AMATCHES2(SLICES($A$102#,HSTACK(0,ROWS($A$102#)),{1,2,3,4,5}),transHistoryTbl[[Date]:[Net Amount]],-1,,{1,2,3,4,5})

giving

69
70
71
72
73
74
75
76
77
78
79
69
70
71
72
73
74
75
76
77
78
79

but this fails:

=AMATCHES2(SLICES($A$102#,HSTACK(0,ROWS($A$102#)),{1,3,4,5,6}),transHistoryTbl[[Date]:[Net Amount]],-1,,{1,3,4,5,6})

giving #N/A.

I think the 2nd formula should work, because it is using the same column numbers for criteria and to look in [and the criteria are a straight filter of the source data for uniqueness,

where A102 contains:

=LET(zsrc,LISTDISTINCTS.COUNT(transHistoryTbl[[Date]:[Net Amount]],,,,,),FILTER(zsrc,INDEX(zsrc,0,11)>1))

so this is returning distinct rows for which there are duplicates,

then AMATCHES2 should give matching positions.

As well as the problem 'can only accept multiple columns if consecutive',

I think that AMATCHES2 can only accept multiple columns if the number of columns is 5 or less (as well as being consecutive).

I think I am going to switch to FILTER.IFS,

but this suffers from having to specify crieria individually,

also I prefer AMATCHES2's ability to accept an array, as an alternative to a range.

Thank you.

John

[John Brown]

Hi Charles, if you have a chance to look into this, I would be very grateful.

Regards

John

[Charles Williams]

I can’t duplicate: this formula works correctly,

=AMATCHES2(HSTACK(C7:C19,E7:E19,G7:G19,H7:H19,J7:J19,K7:K19),A1:K39,1,TRUE,{3,5,7,8,10,11})

 

Maybe your data is not sorted when you choose multiple disjoint columns?

Try using the not sorted option and see if that works.

 

Regards

Charles