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

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


On Jun 8, 10:57 am, CWillis wrote:
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

One way:

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

In article ,
CWillis wrote:

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

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.

" wrote:

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


On Jun 8, 10:57 am, CWillis wrote:
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

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





On Jun 8, 11:21 am, JE McGimpsey wrote:
One way:

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

In article ,



CWillis wrote:
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- Hide quoted text -

- Show quoted text -

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

" wrote:

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





On Jun 8, 11:21 am, JE McGimpsey wrote:
One way:

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

In article ,



CWillis wrote:
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- Hide quoted text -

- Show quoted text -

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.

" wrote:

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





On Jun 8, 11:21 am, JE McGimpsey wrote:
One way:

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

In article ,



CWillis wrote:
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- Hide quoted text -

- Show quoted text -