Dynamic name range using INDEX(array;MATCH(99^99;lookup_array))

[Claudine B]

hi,

I would like to insert a Name via a formula to obtain a Dynamic Range, the range should end with last column (fixed) and last row (flexible and retrievable via 99^99)

I have 1 file where that works, it's an exercise file of a book that I bought - M is for (DATA) Monkeyhereby

the description of the formulas in parts
=MATCH(99^99;$A:$A) result is a number, 42, which is the last row of column A
=INDEX($F:$F;MATCH(99^99;$A:$A)) result is the value of the last row 42 (outcome of match) in column F
=!$A$5:INDEX($F:$F;MATCH(99^99;!$A:$A)) this is the formula in the name manager, as result, name range is A5 : F42

but I have an error, for another file, where I actually want to use this the first step, the match, already gives #N/A as an outcome

what can be the reason for this?

I am aware that this dynamic range could be replaced by a table which is dynamic at once, but I can't because I don't want to have new table formats

Kr Claudine

 

[Bob Phillips]

Claudine,

It is hard to make any useful comment without seeing the offending data, can you post the workbook.

Regarding the table formats, you don't have to accept the defaults, you can use one of man y others or define your own.

 

[Claudine B]

Claudine,

It is hard to make any useful comment without seeing the offending data, can you post the workbook.

Regarding the table formats, you don't have to accept the defaults, you can use one of man y others or define your own.

Hi Bob,

I got the answer in the meanwhile

This formula...

=MATCH(99^99;$A:$A)

...returns the position of the last numerical value in Column A. If there are no numerical values, it returns #N/A. If you in fact have numbers in Column A, but it still returns #N/A, then the numbers are being recognized as text values, instead of numerical values. In this case, you'll need to convert them into numerical values. However, to find the position of the last text value in Column A, you'll need to replace the lookup value as follows...

=MATCH(REPT("z",255);$A:$A)

 

[p45cal]

cross posted without links:
https://www.mrexcel.com/forum/excel...ing-index-array;match-99^99;lookup_array.html
CVlaudine B, for your information, you should always provide links to your cross posts.
This is a requirement, not just a request.
If you have cross posted at other places, please add links to them too.
Why? Have a read of http://www.excelguru.ca/content.php?184

 

[Claudine B]

Hi,
thanks for informing me, I am a new user and was not aware of this, will take care in future
another Q I have is how I can mark this thread as closed, I looked around but can't find
Kr Claudine

 

[AliGW]

There is no thread solved option available here.