software

I built 2 named range for search, for example,
quot;ABCquot; = A1: D4
quot;DEFquot; = A10: F10:

and let's say, if Z1 = 1 then A100 = quot;ABCquot; and
if Z1 = 2 then A100 = quot;DEFquot;

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

quot;RICKYquot; gt; wrote in message ...
gt;I built 2 named range for search, for example,
gt; quot;ABCquot; = A1: D4
gt; quot;DEFquot; = A10: F10:
gt;
gt; and let's say, if Z1 = 1 then A100 = quot;ABCquot; and
gt; if Z1 = 2 then A100 = quot;DEFquot;
gt;
gt; how can have VLOOKUP to look for range ABC or range DEF based on contains in
gt; A100?
gt;
gt; 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

quot;RICKYquot; gt; wrote in message
...
gt;I built 2 named range for search, for example,
gt; quot;ABCquot; = A1: D4
gt; quot;DEFquot; = A10: F10:
gt;
gt; and let's say, if Z1 = 1 then A100 = quot;ABCquot; and
gt; if Z1 = 2 then A100 = quot;DEFquot;
gt;
gt; how can have VLOOKUP to look for range ABC or range DEF based on contains
gt; in
gt; A100?
gt;
gt; Ricky
Just to clarify......

gt;gt; and let's say, if Z1 = 1 then A100 = quot;ABCquot; and
gt;gt; if Z1 = 2 then A100 = quot;DEFquot;

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

Biff

quot;Biffquot; gt; wrote in message
...
gt; Hi!
gt;
gt; Try this:
gt;
gt; =VLOOKUP(lookup_value,CHOOSE(Z1,ABC,DEF),column_in dex_num,0)
gt;
gt; Since your ranges have different widths how do you intend to determine
gt; what the column_index_number is? Or, is it constant?
gt;
gt; Biff
gt;
gt; quot;RICKYquot; gt; wrote in message
gt; ...
gt;gt;I built 2 named range for search, for example,
gt;gt; quot;ABCquot; = A1: D4
gt;gt; quot;DEFquot; = A10: F10:
gt;gt;
gt;gt; and let's say, if Z1 = 1 then A100 = quot;ABCquot; and
gt;gt; if Z1 = 2 then A100 = quot;DEFquot;
gt;gt;
gt;gt; how can have VLOOKUP to look for range ABC or range DEF based on contains
gt;gt; in
gt;gt; A100?
gt;gt;
gt;gt; Ricky
gt;
gt;