Array index, match problem

[Alan Beban]

Alan Beban wrote...
...



Fine. Then consider whether the MakeArray formula,

=INDEX(DataTable,INT((MATCH(J1­9,MakeArray(DataTable,1),0)-1)
/COLUMNS(DataTable))+1,COLUMNS­(DataTable))

a single MATCH against the data range transformed into a 1D array, with
the result adjusted by a division inside INT to return the row number,
against the ArrayMatch formula,

=OFFSET(INDIRECT(ArrayMatch(J­19,dataTable,"A")),0,
-INDEX(ArrayMatch(J19,dataTab­le),1,2)+COLUMNS(dataTable))

first returning the cell address of the matching cell then using
another call to fix the column offset. It's subjective whether the row
index contortions of the MakeArray formula are more obscure than the
column offset contortions of the 2 ArrayMatch formula.

For that matter, you could also have used

=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

which would have been a LOT simpler than either of the others.
Simplicity is good.

Both the MakeArray and the single ArrayMatch formulas involve no
volatile function calls, so they won't cause Excel to prompt users to
save any file containing them if users try to close such workbooks
without making any changes. Your two ArrayMatch formula, due to OFFSET
and INDIRECT calls, would cause such confusing prompts.

Is that an acceptable user consideration?

It's certainly more constructive, particularly the suggestion of

=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

which is significantly faster than the one including the two ArrayMatch
function calls that I originally posted. Would have been nice had you
focused on the instructional value for the users in the first place,
rather than just on stroking your ego. But then, there you go!

By the way, the formula with the MakeArray function call seems to return
an error if the data table exceeds 65536 elements (I haven't yet
identified why; it might be fixable), while neither of the ArrayMatch
formulas seems to--though they are slower.

Alan Beban

 

[Alan Beban]

Harlan Grove wrote:
. . .Then consider whether the MakeArray formula,
an error if the data table exceeds 65536 elements . . . .

The problem is with the built-in INDEX function; it fails if the array
or reference contains more than 65536 elements.

Alan Beban

 

[Harlan Grove]

Alan Beban wrote...
....

The problem is with the built-in INDEX function; it fails if the array
or reference contains more than 65536 elements.

Are you sure there isn't a problem with 65,536 elements? I'd suspect it
chokes after 65,535 elements.

Which would argue in favor of using the COUNTIF function provided in
another branch of this thread. It's one drawback is the volatile OFFSET
call. It'd always recalc, but it'd be lots faster than even a single
udf call.

 

[Harlan Grove]

Alan Beban wrote...
....

=INDEX(DataTable,INDEX(ArrayMatch(J19,DataTable),1),COLUMNS(DataTable))

which is significantly faster than the one including the two ArrayMatch
function calls that I originally posted. Would have been nice had you
focused on the instructional value for the users in the first place,
rather than just on stroking your ego. But then, there you go!

Of course you could have offerred it too, if you had thought of it. But
then, there you go!

By the way, the formula with the MakeArray function call seems to return
an error if the data table exceeds 65536 elements (I haven't yet
identified why; it might be fixable), while neither of the ArrayMatch
formulas seems to--though they are slower.

Excel can't handle any arrays with more than 65,535 entries in either
of 1 or 2 dimensions, as you should know. It's questionable whether
anyone should try to use brute force matching on so many cells. It'd be
slow even without any udfs or volatile functions. There are tasks for
which indexed database searches would be far more appropriate than
unindexed spreadsheet searches. If the OP has so many entries to
search, the OP is being foolish using a spreadsheet for the task.
However, if the OP is only searching a few hundred entries or fewer,
your caveat provides completeness of specification but is of no
practical relevance.

 

[Alan Beban]

Alan Beban wrote...
...



Are you sure there isn't a problem with 65,536 elements? I'd suspect it
chokes after 65,535 elements. . . .

Dim arr, i
ReDim arr(1 To 65536)
For i = 1 To 65536
arr(i) = i * 2
Next
MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

In xl2000 and earlier it fails on 5462 elements or greater.

Alan Beban

 

[Alan Beban]

Dim arr, i
ReDim arr(1 To 65536)
For i = 1 To 65536
arr(i) = i * 2
Next
MsgBox Application.Index(arr, 65536) <----displays 131072 in xl2002.

In xl2000 and earlier it fails on 5462 elements or greater.

Alan Beban

Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
xl2000 or xl2002).

Alan Beban

 

[Harlan Grove]

Alan Beban wrote...

Although on the worksheet =INDEX(A:B, 65536,2) works fine (in either
xl2000 or xl2002).

Not comparable. A:B is a range, not an array. But you're correct that
INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
subsequent arguments and converts them to long integers.

 

[Alan Beban]

Alan Beban wrote...



Not comparable. A:B is a range, not an array. But you're correct that
INDEX can handle 65536 entries. Guess INDEX takes doubles as 2nd and
subsequent arguments and converts them to long integers.

It seems that the limitation on the VBA invocation of the INDEX function
is a bit subtler; it is not limited by the number of the elements in the
array (see arr1 below) but apparently by the number of elements in a
dimension.

Sub testIt3a()
Dim arr1, arr2, arr3
Dim i As Long, j As Long
Dim x, y, z

'65536 rows, 2 columns
ReDim arr1(1 To 65536, 1 To 2)
For i = 1 To 65536: For j = 1 To 2
arr1(i, j) = i * 2 + j
Next: Next

'1 row, 65536 columns
ReDim arr2(1 To 65536)
For i = 1 To 65536
arr2(i) = i
Next

'1 row, 65537 columns
ReDim arr3(1 To 65537)
For i = 1 To 65537
arr3(i) = i
Next

x = Application.Index(arr1, 65536, 2)
Debug.Print x '<---returns 131074
y = Application.Index(arr2, 65536)
Debug.Print y '<---returns 65536
z = Application.Index(arr3, 65536) 'Type mismatch error
End Sub

Alan Beban