function to retrieve array name

M

Monty

i have a spread sheet with array names in each cell

I have a working formula to retrieve the correct array name


The question
=VLOOKUP(U51,MFFC!$A$2:$M$11,2)

U51 is the cell for an age group (17)
the spread sheet MFFC has the age groups starting the row and the cells
contain array names.

curlup pushups run swim bike eliptical
17 curlpt17 curltl17 pupt17 putl17 runpt17 runtl17 swimpt17 swimtl17 bikept17 biketl17 elippt17 eliptl17

the problem is that once retrieved, i cannot get the function to use the
array name because it is deemed as just text and not recognized as a valid
array name.

this is the last step to automate my form and it is proving formidable. my
only other alternative is to cut and paste the formula from another matching
cell.
 
J

Jacob Skaria

The below would reference the array...

=INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0))

If this post helps click Yes
 
T

T. Valko

How do you intend to use the array name? How is the array defined?

If the VLOOKUP returns a text string that represents a defined name this is
how you need to express it:

INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2))
 
M

Monty

=SUMIF(curlpt45,N52,curltl45)
this is the final formula but the array names need swapped out with the
value derived from =VLOOKUP(U51,MFFC!$A$2:$M$11,2) which should be the value
"curlpt45" because U51 contains the number 45

for example
=SUMIF(=VLOOKUP(U51,MFFC!$A$2:$M$11,2),N52,=VLOOKUP(U51,MFFC!$A$2:$M$11,2))

I have been manually typing in the curlpt45 into the formula. when the age
group changes in U column then the curlpt## has to change to match for the
formula to calculate the correct value from the SUMIF.

=INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) returned 100 and it should have
been curlpt45
 
M

Monty

=SUMIF(curlpt45,N52,curltl45)
this is the final formula but the array names need swapped out with the
value derived from =VLOOKUP(U51,MFFC!$A$2:$M$11,2) which should be the value
"curlpt45" because U51 contains the number 45

for example
=SUMIF(=VLOOKUP(U51,MFFC!$A$2:$M$11,2),N52,=VLOOKUP(U51,MFFC!$A$2:$M$11,2))

I have been manually typing in the curlpt45 into the formula. when the age
group changes in U column then the curlpt## has to change to match for the
formula to calculate the correct value from the SUMIF.

=INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) returned 100 and it should have
been curlpt45
 
T

T. Valko

Try it like this:

=SUMIF(INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)),N52,curltl45)

Note however, this will not work if the named range curlpt45 is defined
using functions like OFFSET and INDEX.
 
J

Jacob Skaria

If VLOOKUP(U51,MFFC!$A$2:$M$11,2,0)) returns the text 'curlpt45'

=INDIRECT(VLOOKUP(U51,MFFC!$A$2:$M$11,2,0))

will refer this range..

If 'curlpt45' refers to a single cell and the cell value is 100 then it
returns this cell value


If this post helps click Yes
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Index using a defined name array 1
using a defined Name in a function 2
using strings in vlookup function calls 5
Can the Excel RTD function return an array (It does on Bloomberg) 0
Vlookup and return sheet name also 2
Reverse a function to return the array? 4
How can I use and indexed value as the name of an array for vlooku 2
INDIRECT with Sheet Level Defined Name 2

Top