cell address

M

mike allen

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
 
P

Pete McCosh

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
 
M

mike allen

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.
 
P

Pete McCosh

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
 
Top