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
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" <[email protected]> wrote in message news:[email protected]...
>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_index_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" <[email protected]> wrote in message
news:[email protected]...
>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" <[email protected]> wrote in message
news:[email protected]...
> Hi!
>
> Try this:
>
> =VLOOKUP(lookup_value,CHOOSE(Z1,ABC,DEF),column_index_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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks