vlookup, concatenated named range
 

My function:

=VLOOKUP(G7,"Asset"&F7,1,FALSE)

"Asset"&F7 is a concatenation for a named range. If i type in manually, it
works. Concatenated, it does not.

Why does this not work? Not sure how or if to use indirect or offset.

vlookup, concatenated named range
 

Patti,

Try this
=VLOOKUP(G7,INDIRECT("Asset"&F7),1,FALSE)
--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.


"patti" wrote:

My function:

=VLOOKUP(G7,"Asset"&F7,1,FALSE)

"Asset"&F7 is a concatenation for a named range. If i type in manually, it
works. Concatenated, it does not.

Why does this not work? Not sure how or if to use indirect or offset.

vlookup, concatenated named range
 

Why does this not work?

Let's assume F7 = 2009

"Asset"&F7 = "Asset2009". When you concatenate you're creating a *TEXT*
string. Even though you may have a valid named range called Asset2009 these
are not the same thing.

INDIRECT will convert a TEXT representation of a reference into a valid
reference that can be used as function arguments.

=VLOOKUP(G7,INDIRECT("Asset"&F7),1,0)

This will not work if the named range is a dynamic range defined with
functions like OFFSET.

--
Biff
Microsoft Excel MVP


"patti" wrote in message
...
My function:

=VLOOKUP(G7,"Asset"&F7,1,FALSE)

"Asset"&F7 is a concatenation for a named range. If i type in manually, it
works. Concatenated, it does not.

Why does this not work? Not sure how or if to use indirect or offset.

vlookup, concatenated named range
 

Thanks Mike and Biff.
Zippy and educating answers.

"patti" wrote:

My function:

=VLOOKUP(G7,"Asset"&F7,1,FALSE)

"Asset"&F7 is a concatenation for a named range. If i type in manually, it
works. Concatenated, it does not.

Why does this not work? Not sure how or if to use indirect or offset.

vlookup, concatenated named range
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"patti" wrote in message
...
Thanks Mike and Biff.
Zippy and educating answers.

"patti" wrote:

My function:

=VLOOKUP(G7,"Asset"&F7,1,FALSE)

"Asset"&F7 is a concatenation for a named range. If i type in manually,
it
works. Concatenated, it does not.

Why does this not work? Not sure how or if to use indirect or offset.