Hopefully a simple vlookup question...

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

if i have prenamed 4 ranges: spring,summer,fall,winter And I have a formula as stated below..

=vlookup(a9,b9,5,0

where a9 is the value to be looked up, and b9 is the input of the user which will have to be(spring, summer, fall, or winter), and 5 obviously the column to pull the information from.

Is there a way to get excel to use the typed value in b9 of the user to correspond to the prenamed ranges?
 
Hi
try
=vlookup(a9,INDIRECT(b9),5,0)


--
Regards
Frank Kabel
Frankfurt, Germany
paul said:
if i have prenamed 4 ranges: spring,summer,fall,winter And I
Click to expand...
have a formula as stated below...
=vlookup(a9,b9,5,0)

where a9 is the value to be looked up, and b9 is the input of the
Click to expand...
user which will have to be(spring, summer, fall, or winter), and 5
obviously the column to pull the information from.
Is there a way to get excel to use the typed value in b9 of the user
Click to expand...
to correspond to the prenamed ranges?
 
Paul,

Use =vlookup(a9,indirect(b9),5,0)

in stead of :

=vlookup(a9,b9,5,0)


--
Regards,
Auk Ales

* Please reply to this newsgroup only *
* I will not react on unsolicited e-mails *

paul said:
if i have prenamed 4 ranges: spring,summer,fall,winter And I have a formula as stated below...

=vlookup(a9,b9,5,0)

where a9 is the value to be looked up, and b9 is the input of the user
Click to expand...
which will have to be(spring, summer, fall, or winter), and 5 obviously the
column to pull the information from.
Is there a way to get excel to use the typed value in b9 of the user to
Click to expand...
correspond to the prenamed ranges?
 
Paul

=VLOOKUP(A9,INDIRECT(B9),5,0)

Or, the more complex version that caters for
a) the value not being in the table (#NA!) and
b) invalid user input (#REF!):

=IF(ISNA(VLOOKUP(A9,INDIRECT(B9),5,0)), "Not in Table",
IF(ISERROR(VLOOKUP(A9,INDIRECT(B9),5,0)), "Invalid Season",
VLOOKUP(A9,INDIRECT(B9),5,0)))

Regards

Trevor


paul said:
if i have prenamed 4 ranges: spring,summer,fall,winter And I have a formula as stated below...

=vlookup(a9,b9,5,0)

where a9 is the value to be looked up, and b9 is the input of the user
Click to expand...
which will have to be(spring, summer, fall, or winter), and 5 obviously the
column to pull the information from.
Is there a way to get excel to use the typed value in b9 of the user to
Click to expand...
correspond to the prenamed ranges?
 
Back
Top