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