| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
use the result of match function as a part of row address
I want to use the result of match function as a part of row address.
that is; if "=MATCH(B7,$B$1:$B$7,0)" gives 3 then $a$(MATCH(B7,$B$1:$B$7,0)) things like that. is it possible? |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
use the result of match function as a part of row address
=INDEX($A$1:$A$7, MATCH(B7,$B$1:$B$7,0))
or =INDIRECT("A"&MATCH(B7,$B$1:$B$7,0)) but I usually prefer index for referencing another cell or doing some sort of lookup as Indirect is volatile (recalculates every time XL does) and, if you were to move the data in column A (cut/paste or inserting a column and shifting column A to column B), the indirect function will not adjust itself to reference the new location of the data. Or, depending on your data, that may be exactly what you want. "kang" wrote: I want to use the result of match function as a part of row address. that is; if "=MATCH(B7,$B$1:$B$7,0)" gives 3 then $a$(MATCH(B7,$B$1:$B$7,0)) things like that. is it possible? |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
use the result of match function as a part of row address
="$A$"&MATCH(B7,$B$1:$B$7,0)
"kang" wrote: I want to use the result of match function as a part of row address. that is; if "=MATCH(B7,$B$1:$B$7,0)" gives 3 then $a$(MATCH(B7,$B$1:$B$7,0)) things like that. is it possible? |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
use the result of match function as a part of row address
Thanks a million.
JMB wrote: =INDEX($A$1:$A$7, MATCH(B7,$B$1:$B$7,0)) or =INDIRECT("A"&MATCH(B7,$B$1:$B$7,0)) but I usually prefer index for referencing another cell or doing some sort of lookup as Indirect is volatile (recalculates every time XL does) and, if you were to move the data in column A (cut/paste or inserting a column and shifting column A to column B), the indirect function will not adjust itself to reference the new location of the data. Or, depending on your data, that may be exactly what you want. "kang" wrote: I want to use the result of match function as a part of row address. that is; if "=MATCH(B7,$B$1:$B$7,0)" gives 3 then $a$(MATCH(B7,$B$1:$B$7,0)) things like that. is it possible? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| search spreadsheet for 'xyz' as part of any hyperlink address | Excel Discussion (Misc queries) | |||
| VLOOKUP with cell address as part of the argument | Excel Discussion (Misc queries) | |||
| Using address function result in an array | Excel Worksheet Functions | |||
| Replace part of link address in a cell with a value(date) from ano | Excel Discussion (Misc queries) | |||
| Using result from ADDRESS function as a cell reference itself | Excel Worksheet Functions | |||
Linear Mode
