I built 2 named range for search, for example,
"ABC" = A1: D4
"DEF" = A10: F10:

and let's say, if Z1 = 1 then A100 = "ABC" and
if Z1 = 2 then A100 = "DEF"

how can have VLOOKUP to look for range ABC or range DEF based on contains in
A100?

Ricky

Hi Ricky,

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten

"RICKY" wrote in message ...
I built 2 named range for search, for example,
"ABC" = A1: D4
"DEF" = A10: F10:

and let's say, if Z1 = 1 then A100 = "ABC" and
if Z1 = 2 then A100 = "DEF"

how can have VLOOKUP to look for range ABC or range DEF based on contains in
A100?

Ricky

Hi!

Try this:

=VLOOKUP(lookup_value,CHOOSE(Z1,ABC,DEF),column_in dex_num,0)

Since your ranges have different widths how do you intend to determine what
the column_index_number is? Or, is it constant?

Biff

"RICKY" wrote in message
...
I built 2 named range for search, for example,
"ABC" = A1: D4
"DEF" = A10: F10:

and let's say, if Z1 = 1 then A100 = "ABC" and
if Z1 = 2 then A100 = "DEF"

how can have VLOOKUP to look for range ABC or range DEF based on contains
in
A100?

Ricky

Just to clarify......

and let's say, if Z1 = 1 then A100 = "ABC" and
if Z1 = 2 then A100 = "DEF"

If you use the formula I suggested you don't need to reference A100 in the
lookup.

Biff

"Biff" wrote in message
...
Hi!

Try this:

=VLOOKUP(lookup_value,CHOOSE(Z1,ABC,DEF),column_in dex_num,0)

Since your ranges have different widths how do you intend to determine
what the column_index_number is? Or, is it constant?

Biff

"RICKY" wrote in message
...
I built 2 named range for search, for example,
"ABC" = A1: D4
"DEF" = A10: F10:

and let's say, if Z1 = 1 then A100 = "ABC" and
if Z1 = 2 then A100 = "DEF"

how can have VLOOKUP to look for range ABC or range DEF based on contains
in
A100?

Ricky