VLookup Range Help

[Guest]

I would like to have the "Range" portion of the VLOOKUP command be populated
from a user input. Is this possible?

Ex: A2 has the formula VLOOKUP(1,A4:C12,1,TRUE) works. Instead, I would
like for cell A1 to have the value: "A4:C12" and the formula in A2 to be
VLOOKUP(1,A1,1,TRUE). I would like to avoid macros.

Thanks in advance.

-Chris

 

[krcowen]

Chris

Try something like:

=VLOOKUP(1,INDIRECT(A1),1,TRUE)

with the range of interest in cell A1, e.g. A4:C12.

Good luck.

Ken
Norfolk, Va

 

[JE McGimpsey]

One way:

A2: =VLOOKUP(1, INDIRECT(A1),1, TRUE)

 

[Guest]

Thanks Ken. It works great for my example, but I should have made my example
closer to my actual problem. Lets move cell A1 to a different sheet. The
value of A1 also references a range in a completely different workbook. I
can't get INDIRECT to accept this. Thanks again.

 

[krcowen]

Chris

The same methodology still works for me when I have:

=VLOOKUP(B1,INDIRECT(Sheet2!A1),1,FALSE)

in cell C1 on sheet1 of Book1

and

[Book2]Sheet1!$A$1:$A$10

in A1 of sheet2 of Book1

and my lookup range on Sheet1 of Book2.

Ken

 

[Guest]

Ken,
That works for me too as long as Book 2 is open. If I close Book 2 and
replace A1 with the full path name to Book 2 (C:\Book 2) then it gives a
#REF!. Any ideas?
-Chris

Chris

The same methodology still works for me when I have:

=VLOOKUP(B1,INDIRECT(Sheet2!A1),1,FALSE)

in cell C1 on sheet1 of Book1

and

[Book2]Sheet1!$A$1:$A$10

in A1 of sheet2 of Book1

and my lookup range on Sheet1 of Book2.

Ken

One way:

A2: =VLOOKUP(1, INDIRECT(A1),1, TRUE)






- Show quoted text -

 

[Guest]

I should have read the help file better for the INDIRECT command. The file
has to be open. I guess I will be writing a macro. Thanks again.

Chris

The same methodology still works for me when I have:

=VLOOKUP(B1,INDIRECT(Sheet2!A1),1,FALSE)

in cell C1 on sheet1 of Book1

and

[Book2]Sheet1!$A$1:$A$10

in A1 of sheet2 of Book1

and my lookup range on Sheet1 of Book2.

Ken

One way:

A2: =VLOOKUP(1, INDIRECT(A1),1, TRUE)






- Show quoted text -