MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Using value returned by MATCH as an Index


Posted by Eric Snow on May 23, 2001 10:44 AM

I want to use the value returned by by a MATCH call as the index for a cell, such as instead of $A$14 have $A$MATCH(...), except that doesn't work. Any ideas on how to get it to work?

Thanks


Posted by Aladin Akyurek on May 23, 2001 10:56 AM

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

Posted by Barrie Davidson on May 23, 2001 11:01 AM

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

Posted by Eric Snow on May 23, 2001 11:07 AM

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.

Posted by Barrie Davidson on May 23, 2001 11:12 AM

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

Barrie

Posted by Eric Snow on May 23, 2001 11:33 AM

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.

Posted by Aladin Akyurek on May 23, 2001 11:41 AM

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: