cell address
 

i have an array in cells f8:n16 with letters across top row and numbers
along left column. these numbers and letters are not in any particular
order. the middle of this array has various information that i need to
retrieve, given a letter (that is on top row) and number (that is on left
column). i would like the cell address (row and column numbers) of the
intersection of these 2 inputs. example: 1st input = p, 2nd input=7. p is
in column m, or 13th row in spreadsheet, while 7 is in row 15. thus, i
would like to have cells(15,13) as my answer, which would correspond w/ what
i am trying to retrieve. thanks, mike allen

cell address
 

Mike,

your simplest solution is to use the INDEX and MACTH
functions:

=index(f8:n16,MATCH(4,F8:F16,0),MATCH("g",F8:N8,0) )

where the values in the first part of the two MATCH
functions can be replaced with another cell reference to
allow you to vary the input.

Cheers, Pete

-----Original Message-----
i have an array in cells f8:n16 with letters across top
row and numbers
along left column. these numbers and letters are not in
any particular
order. the middle of this array has various information
that i need to
retrieve, given a letter (that is on top row) and number
(that is on left
column). i would like the cell address (row and column
numbers) of the
intersection of these 2 inputs. example: 1st input = p,
2nd input=7. p is
in column m, or 13th row in spreadsheet, while 7 is in
row 15. thus, i
would like to have cells(15,13) as my answer, which would
correspond w/ what
i am trying to retrieve. thanks, mike allen


.

cell address
 

thanks so much for the useful info. focusing just on the 'match' function
and taking it a step further, how can i tell that my exact match is not only
on the 5th row of my array, but also on the 12th row of the spreadsheet
(assuming array is started on row8, or 7 other rows above array), other than
simply adding 7 (array may move, etc.)? i have tried 'row' and 'column' and
'address'... to no avail. thanks.
"Pete McCosh" wrote in message
...
Mike,

your simplest solution is to use the INDEX and MACTH
functions:

=index(f8:n16,MATCH(4,F8:F16,0),MATCH("g",F8:N8,0) )

where the values in the first part of the two MATCH
functions can be replaced with another cell reference to
allow you to vary the input.

Cheers, Pete

-----Original Message-----
i have an array in cells f8:n16 with letters across top
row and numbers
along left column. these numbers and letters are not in
any particular
order. the middle of this array has various information
that i need to
retrieve, given a letter (that is on top row) and number
(that is on left
column). i would like the cell address (row and column
numbers) of the
intersection of these 2 inputs. example: 1st input = p,
2nd input=7. p is
in column m, or 13th row in spreadsheet, while 7 is in
row 15. thus, i
would like to have cells(15,13) as my answer, which would
correspond w/ what
i am trying to retrieve. thanks, mike allen


.

cell address
 

Mike,

if I understand you right, there may be two entries with
the same value in your index column? The best place to
start with this kind of thing (as with so many others) is
Chip Pearson's page:

http://www.cpearson.com/excel/lookups.htm

The section on "Arbitrary Lookups" might be of particular
interest.

Pete

-----Original Message-----
thanks so much for the useful info. focusing just on
the 'match' function
and taking it a step further, how can i tell that my
exact match is not only
on the 5th row of my array, but also on the 12th row of
the spreadsheet
(assuming array is started on row8, or 7 other rows above
array), other than
simply adding 7 (array may move, etc.)? i have
tried 'row' and 'column' and
'address'... to no avail. thanks.
"Pete McCosh" wrote in message
...
Mike,

your simplest solution is to use the INDEX and MACTH
functions:

=index(f8:n16,MATCH(4,F8:F16,0),MATCH("g",F8:N8,0) )

where the values in the first part of the two MATCH
functions can be replaced with another cell reference to
allow you to vary the input.

Cheers, Pete

-----Original Message-----
i have an array in cells f8:n16 with letters across top
row and numbers
along left column. these numbers and letters are not
in
any particular
order. the middle of this array has various
information
that i need to
retrieve, given a letter (that is on top row) and
number
(that is on left
column). i would like the cell address (row and column
numbers) of the
intersection of these 2 inputs. example: 1st input =
p,
2nd input=7. p is
in column m, or 13th row in spreadsheet, while 7 is in
row 15. thus, i
would like to have cells(15,13) as my answer, which
would
correspond w/ what
i am trying to retrieve. thanks, mike allen