Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
=HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),QuoteNotes, | Excel Worksheet Functions | |||
=HYPERLINK("#"&CELL("address", ADDRESS(MATCH(VALUE(B3),Range ... ? | Excel Worksheet Functions | |||
Return cell address of a cell based on contents of cell. | Excel Worksheet Functions | |||
How to create table of cell names with the name's cell address | Excel Discussion (Misc queries) | |||
How make hyperlink refer to cell content rather than cell address. | Excel Discussion (Misc queries) |