VLookup Range Help

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

CWillis - 08 Jun 2007 15:57 GMT

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

Reply to this Message

krcowen@aol.com - 08 Jun 2007 16:17 GMT

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

> I would like to have the "Range" portion of the VLOOKUP command be populated
> from a user input. Is this possible?
[quoted text clipped - 6 lines]
>
> -Chris

Reply to this Message

CWillis - 08 Jun 2007 16:31 GMT

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.

> Chris
>
[quoted text clipped - 19 lines]
> >
> > -Chris

Reply to this Message

JE McGimpsey - 08 Jun 2007 16:21 GMT

One way:

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

> I would like to have the "Range" portion of the VLOOKUP command be populated
> from a user input. Is this possible?
[quoted text clipped - 6 lines]
>
> -Chris

Reply to this Message

krcowen@aol.com - 08 Jun 2007 17:19 GMT

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:
>
[quoted text clipped - 14 lines]
>
> - Show quoted text -

Reply to this Message

CWillis - 08 Jun 2007 18:19 GMT

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
>
[quoted text clipped - 32 lines]
> >
> > - Show quoted text -

Reply to this Message

CWillis - 08 Jun 2007 19:06 GMT

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
>
[quoted text clipped - 32 lines]
> >
> > - Show quoted text -

Reply to this Message