Using value returned by MATCH as an Index

Not sure, but you apparently want to build a cell address where the number MATCH returns is the row number. If so:

="$A$"&MATCH(...) or

=ADDRESS(MATCH(...),1) where 1 reference column A.

Hope this helps.

Aladin

Eric, this formula would return the same as =$A$14 if the match formula returned 14.
=INDIRECT("$a$"&MATCH(10,B2:B8,1),1)

This formula assumes you are looking for "10" in your match and your lookup range is B2:B8. Make the appropriate changes for you purposes.

Hope this helps
Barrie

I have a function in a cell, and what I'm trying to do is to compute over a variable amount of rows. I have a call to MATCH, MATCH("A",A:A,0) which returns the row number where A is located. I'm then trying to retrieve information in a range based on that return. Currently I have:
INDEX($A$3:$C$11, MATCH(A15,$A$3:$A$11,0), 2)
But instead of $C$11 I want $C${Value return by MATCH call}

Excel97 doesn't seem to like either of your suggestions.

Basically, I want the range computed there to expand when I insert rows at the bottom of the data. Excel makes the appropriate adjustments when rows are inserted inside the data, just not outside.

Thanksfor the help, sorry for not being more specific in the first place.

How about this,
=INDEX("$A$3:$C$"&MATCH(A15,$A$3:$A$11,0), 2)

Barrie

This does not seem to be working either as I am getting the reserved word #VALUE! returned.

But I solved the problem by just expanding the area to include an additional empty row beneath the data, so when a row is inserted it expands itself.

Good 'nuff.

Thanks a lot for the help.

Eric

Not too fast. Suppose you have

{1;2;3;4;7;8;5}

occupying the cells in A from A1 on.

="$A$"&MATCH(4,A1:A7,0)

will give you

$A$4

So will

=ADDRESS(MATCH(4,A1:A7,0),1)

I suggested these 2 formulas, based on my understanding of your query.

Back to your query:

I think you need to retrieve a value from a range that spans from A to C where the number of rows containing data changes by additions and/or deletions. If so, create a named dynamic range, one that is global (usual way) or local (my way). I give you the usual method.

Activate cell A3 on the relevant sheet. Activate Insert|Name|Define. Enter as name, say DATA. Enter the following formula as value of Refers to:

=OFFSET(x!$A$3,0,0,COUNTA($A:$A),3)

Repeat the process where you define LVALUES with Refers to:

=OFFSET(x!$A$3,0,0,COUNTA($A:$A),1)

Your formula

INDEX($A$3:$C$11, MATCH(A15,$A$3:$A$11,0), 2)

can be changed to

=INDEX(DATA,MATCH(lookup-value,LVALUES,0),2)

I must admit I'm still unsure about the nature of your query. I still wonder about A15 that you use as lookup-value in the MATCH-part.

You can of course post a snippet of your data, along with the desired result to help us understand.

Aladin

=================== I have a function in a cell, and what I'm trying to do is to compute over a variable amount of rows. I have a call to MATCH, MATCH("A",A:A,0) which returns the row number where A is located. I'm then trying to retrieve information in a range based on that return. Currently I have: