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