Location of Large

[Moni]

I'm using the formula: "=LARGE(('FY04'!W2,'FY04'!
T16,'FY04'!N27,'FY04'!N39,'FY04'!Q54,'FY04'!N66,'FY04'!
Q77,'FY04'!K114,'FY04'!H128,'FY04'!T168,'FY04'!
N181,'FY04'!K192,'FY04'!D182),1)" I would like to find
the formula and syntax to locate the "cell of choice" so
that I can add offset to the formula and retrieve
information in column C of that row. Any ideas? I
prefer not to use Macros, but would like to learn the
actual UDF. Thanks.

 

[Harlan Grove]

I'm using the formula: "=LARGE(('FY04'!W2,'FY04'!
T16,'FY04'!N27,'FY04'!N39,'FY04'!Q54,'FY04'!N66,'FY04'!
Q77,'FY04'!K114,'FY04'!H128,'FY04'!T168,'FY04'!
N181,'FY04'!K192,'FY04'!D182),1)" I would like to find
the formula and syntax to locate the "cell of choice" so
that I can add offset to the formula and retrieve
information in column C of that row. Any ideas? I
prefer not to use Macros, but would like to learn the
actual UDF. Thanks.

One possibility involves putting the addresses for these disjoint,
nonadjacent cells into an array of strings, then wrapping that inside
INDIRECT. For example,

=INDEX({"'FY04'!W2","'FY04'!T16","'FY04'!N27","'FY04'!N39","'FY04'!Q54",
"'FY04'!N66","'FY04'!Q77","'FY04'!K114","'FY04'!H128","'FY04'!T168",
"'FY04'!N181","'FY04'!K192","'FY04'!D182"},
MATCH(LARGE(N(INDIRECT({"'FY04'!W2","'FY04'!T16","'FY04'!N27",
"'FY04'!N39","'FY04'!Q54","'FY04'!N66","'FY04'!Q77","'FY04'!K114",
"'FY04'!H128","'FY04'!T168","'FY04'!N181","'FY04'!K192","'FY04'!D182"})),
1),N(INDIRECT({"'FY04'!W2","'FY04'!T16","'FY04'!N27","'FY04'!N39",
"'FY04'!Q54","'FY04'!N66","'FY04'!Q77","'FY04'!K114","'FY04'!H128",
"'FY04'!T168","'FY04'!N181","'FY04'!K192","'FY04'!D182"})),0))

Even better would be to enter these addresses in another, single area range
like X1:X13, then use the formula

=INDEX(X1:X13,MATCH(LARGE(N(INDIRECT(X1:X13)),1),N(INDIRECT(X1:X13)),0))