Excel 2003. Trying to enter an INDEX function. I put in the proper formula
(checked, and re-checked) Then, instead of simply hitting "enter, I hit
"Control-Shift-Enter"

Very seldom does has this actually worked for me, and not at all lately.
What am I doing wrong?

The Index() function does not necessarily need to be array entered.
It depends on the entire formula.

SO ... how about sharing your formula with us so that we might offer you
some suggestions?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jaytee" wrote in message
...
Excel 2003. Trying to enter an INDEX function. I put in the proper formula
(checked, and re-checked) Then, instead of simply hitting "enter, I hit
"Control-Shift-Enter"

Very seldom does has this actually worked for me, and not at all lately.
What am I doing wrong?

=INDEX(Entry!J$4:J$999,SMALL(IF(Entry!$A$4:$A$999= $B$4,ROW($1:$995)),ROW(1:1)))

The idea was to input data onto the Entry sheet, and access it on the Owners
sheet by selecting a name from a drop down list in B4 of the owners sheet.

"Ragdyer" wrote:

The Index() function does not necessarily need to be array entered.
It depends on the entire formula.

SO ... how about sharing your formula with us so that we might offer you
some suggestions?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jaytee" wrote in message
...
Excel 2003. Trying to enter an INDEX function. I put in the proper formula
(checked, and re-checked) Then, instead of simply hitting "enter, I hit
"Control-Shift-Enter"

Very seldom does has this actually worked for me, and not at all lately.
What am I doing wrong?

You haven't stated exactly what your actual problem was - error message,
wrong data return, no data return - or what?

I'm assuming that you're getting an error message, maybe #N/A ?
Probably because your array is 996 rows and you've only referenced 995 rows
in your formula.

Does that sound right to you?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jaytee" wrote in message
...

=INDEX(Entry!J$4:J$999,SMALL(IF(Entry!$A$4:$A$999= $B$4,ROW($1:$995)),ROW(1:1
)))

The idea was to input data onto the Entry sheet, and access it on the
Owners
sheet by selecting a name from a drop down list in B4 of the owners sheet.

"Ragdyer" wrote:

The Index() function does not necessarily need to be array entered.
It depends on the entire formula.

SO ... how about sharing your formula with us so that we might offer you
some suggestions?
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Jaytee" wrote in message
...
Excel 2003. Trying to enter an INDEX function. I put in the proper
formula
(checked, and re-checked) Then, instead of simply hitting "enter, I
hit
"Control-Shift-Enter"

Very seldom does has this actually worked for me, and not at all
lately.
What am I doing wrong?

Yep, exactly right. The wrong row reference (Say that three times fast) is
the primary problem. For some reason the array reference and row reference
"change themselves" when I close and reopen, so when I try to repair the
numbers (Should be from 4 to 999 in the first two references, and 1 to 995 in
the last one) I can't get it to accept the "Control-Shift- Delete" dealie.

"Ragdyer" wrote:

You haven't stated exactly what your actual problem was - error message,
wrong data return, no data return - or what?

I'm assuming that you're getting an error message, maybe #N/A ?
Probably because your array is 996 rows and you've only referenced 995 rows
in your formula.

Does that sound right to you?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jaytee" wrote in message
...

=INDEX(Entry!J$4:J$999,SMALL(IF(Entry!$A$4:$A$999= $B$4,ROW($1:$995)),ROW(1:1
)))

The idea was to input data onto the Entry sheet, and access it on the
Owners
sheet by selecting a name from a drop down list in B4 of the owners sheet.

"Ragdyer" wrote:

The Index() function does not necessarily need to be array entered.
It depends on the entire formula.

SO ... how about sharing your formula with us so that we might offer you
some suggestions?
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Jaytee" wrote in message
...
Excel 2003. Trying to enter an INDEX function. I put in the proper
formula
(checked, and re-checked) Then, instead of simply hitting "enter, I
hit
"Control-Shift-Enter"

Very seldom does has this actually worked for me, and not at all
lately.
What am I doing wrong?

<<<"I can't get it to accept the "Control-Shift- Delete" dealie"

Tell me that *THAT* was a typo ! ! !

You didn't really mean *DELETE*, did you ? ? ?
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jaytee" wrote in message
...
Yep, exactly right. The wrong row reference (Say that three times fast) is
the primary problem. For some reason the array reference and row reference
"change themselves" when I close and reopen, so when I try to repair the
numbers (Should be from 4 to 999 in the first two references, and 1 to 995
in
the last one) I can't get it to accept the "Control-Shift- Delete" dealie.

"Ragdyer" wrote:

You haven't stated exactly what your actual problem was - error message,
wrong data return, no data return - or what?

I'm assuming that you're getting an error message, maybe #N/A ?
Probably because your array is 996 rows and you've only referenced 995
rows
in your formula.

Does that sound right to you?
--
Regards,

RD


--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may benefit
!

--------------------------------------------------------------------------
-
"Jaytee" wrote in message
...


=INDEX(Entry!J$4:J$999,SMALL(IF(Entry!$A$4:$A$999= $B$4,ROW($1:$995)),ROW(1:1
)))

The idea was to input data onto the Entry sheet, and access it on the
Owners
sheet by selecting a name from a drop down list in B4 of the owners
sheet.

"Ragdyer" wrote:

The Index() function does not necessarily need to be array entered.
It depends on the entire formula.

SO ... how about sharing your formula with us so that we might offer
you
some suggestions?
--
Regards,

RD



--------------------------------------------------------------------------
-
Please keep all correspondence within the NewsGroup, so all may
benefit
!


--------------------------------------------------------------------------
-
"Jaytee" wrote in message
...
Excel 2003. Trying to enter an INDEX function. I put in the proper
formula
(checked, and re-checked) Then, instead of simply hitting "enter,
I
hit
"Control-Shift-Enter"

Very seldom does has this actually worked for me, and not at all
lately.
What am I doing wrong?

"Jaytee" wrote...
=INDEX(Entry!J$4:J$999,SMALL(IF(Entry!$A$4:$A$999 =$B$4,
ROW($1:$995)),ROW(1:1)))

The idea was to input data onto the Entry sheet, and access it on the
Owners sheet by selecting a name from a drop down list in B4 of the
owners sheet.
....

INDEX has odd semantics in array formulas. Unlike OFFSET, INDEX will only
return arrays when either its first or second argument is zero. Also, ROW
always returns an array, so using ROW is different arguments to SMALL could
also cause problems.

If you want the value in Entry!J4:J999 corresponding to the first entry in
Entry!A4:A999 matching B4 in the calling formula's worksheet, use

=VLOOKUP($B$4,Entry!$A$4:J$999,10,0)

If you want the value in Entry!J4:J999 corresponding to the k_th entry in
Entry!A4:A999 matching B4, use

=OFFSET(Entry!J$4,SMALL(IF(Entry!$A$4:$A$999=$B$4,
ROW(Entry!$A$4:$A$999)),k)-ROW(Entry!$A$4),0)

"Harlan Grove" wrote...
....
INDEX has odd semantics in array formulas. Unlike OFFSET, INDEX will
only return arrays when either its first or second argument is zero.
....

Oops! Make that second or third argument is zero.

Harlan Grove wrote:
"Harlan Grove" wrote...
...

INDEX has odd semantics in array formulas. Unlike OFFSET, INDEX will
only return arrays when either its first or second argument is zero.

...

Oops! Make that second or third argument is zero.


I haven't taken the trouble to understand exactly what the OP is doing
with the original formula (it seemed to work for me in a simple test
when array entered), but the above statement is clearly incorrect. E.g.,

=INDEX(a1:a5,{3,4}), array entered, returns an array, as does
=INDEX(c3:e10,ROW(4:6),2), or, for that matter, the OP's original
formula--though, as Ragdyer pointed out, a problem can arise from the
difference in size of the areas in the formulas.

Alan Beban

Alan Beban wrote...
Harlan Grove wrote:
INDEX has odd semantics in array formulas. Unlike OFFSET, INDEX will
only return arrays when either its first or second argument is zero.
....
I haven't taken the trouble to understand exactly what the OP is doing
with the original formula (it seemed to work for me in a simple test
when array entered), but the above statement is clearly incorrect. E.g.,
....

It's incorrect in part. INDEX does have odd array semantics, but it
does work when it's the outermost function call and is called with
array second or third arguments. However, evaluating
=INDEX({1,4,7;2,5,8;3,6,9},2,{1,2}) in the formula bar (pressing [F9]
with all or none of the formula selected) returns just 2 rather than
{2,5}.

This isn't the source of the OP's trouble, but it's still something to
be avoided. Enter in any cell

=SUM(INDEX({1,4,7;2,5,8;3,6,9},2,{1,2}))

It returns 2 whether entered normally or as an array formula. Select a
2 column by one row range and enter this formula as an array formula,
and it'll return {2,5} rather than {7,7}. Enter

=SUMPRODUCT(INDEX({1,4,7;2,5,8;3,6,9},2,{1,2}))

in a single cell, and it returns 2 whether entered normally or as an
array formula. Select a 2 column by one row range and enter this
formula as an array formula, and it'll return {7,7}.

Harlan Grove wrote:
.. . . Enter in any cell

=SUM(INDEX({1,4,7;2,5,8;3,6,9},2,{1,2}))

It returns 2 whether entered normally or as an array formula. . . .

A few years back you mused that perhaps VLOOKUP and INDEX return some
other sort of collection object than an array. But this certainly
doesn't seem to be the case if these worksheet functions are invoked
through VBA. E.g.:

Function IndexVBA(iRef, iRow, iColumn)
IndexVBA = Application.Index(iRef, iRow, iColumn)
End Function

=SUM(IndexVBA({1,4,7;2,5,8;3,6,9},2,{1,2})) will return 7, whether
entered normally or as an array function.

Alan Beban

Alan Beban wrote...
....
A few years back you mused that perhaps VLOOKUP and INDEX return some
other sort of collection object than an array. But this certainly
doesn't seem to be the case if these worksheet functions are invoked
through VBA. E.g.:

Function IndexVBA(iRef, iRow, iColumn)
IndexVBA = Application.Index(iRef, iRow, iColumn)
End Function
....

Your test is flawed. Just before the End function statement IndexVBA
contains a variant containing an array of variants. The array formula

=TYPE(IndexVBA({1,4,7;2,5,8;3,6,9},2,{1,2}))

returns 64, while the array formula

=TYPE(INDEX({1,4,7;2,5,8;3,6,9},2,{1,2}))

returns 1. Gosh, Alan, what could be the difference?


For that matter, try the nonarray formulas

=ISREF(IndexVBA($A$1:$C$3,2,{1,2}))

which returns FALSE and

=ISREF(INDEX($A$1:$C$3,2,{1,2}))

which returns TRUE.

This is due to your udf implicitly converting the VBA .Index call's
range result to its .Value property. You could try guarding against
that by using


Function IndexVBA1(iRef, iRow, iCol)
If IsObject(iRef) Then
Set IndexVBA1 = Application.Index(iRef, iRow, iCol)
Else
IndexVBA1 = Application.Index(iRef, iRow, iCol)
End If
End Function


But both the nonarray and array formulas

=ISREF(IndexVBA($A$1:$C$3,2,{1,2}))

still return FALSE because the formula

=TYPE(indexvba1($A$1:$C$3,2,{1,2}))

returns 16, error. Note that the nonarray formula

=CELL("Address",INDEX($A$1:$C$3,2,{1,2}))

returns $A$2, and the same formula entered as an array formula in a
2-column by 1-row range returns {"$A$2","$B$2"}. So the worksheet INDEX
function called with a range reference as first argument and an array
as second or third argument returns something that (1) isn't an error
but (2) behaves in part like a range reference.

I await your revised udf providing *EXACTLY* these semantics.