Vlookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Rolohound - 20 Dec 2007 13:40 GMT

I have 10 sheets with different tables on each.
Using If and Vlookup I've exceded my 7 references.
What I would like is to be able to use the text data from a cell
to point to one of the 10 sheets. i.e. so that the cell data is named
the same as one of the sheets and Vlookup uses this in Table_array.

So if the value of cell A2="Wire"
Vlookup(C15,WireA1:C50,2) but if cell A3="Line"
then Vlookup(C15,LineA1:C50,2) etc
Many Thanks

Reply to this Message

Stephen - 20 Dec 2007 14:03 GMT

If none of your sheet names contains any space character:
=VLOOKUP(C15,INDIRECT(A2&"!A1:C50"),2)
Or more generally:
=VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50"),2)
(Note the apostrophe between the double quotes and another one before the
exclamation mark).

>I have 10 sheets with different tables on each.
> Using If and Vlookup I've exceded my 7 references.
[quoted text clipped - 6 lines]
> then Vlookup(C15,LineA1:C50,2) etc
> Many Thanks

Reply to this Message

Rolohound - 20 Dec 2007 14:24 GMT

Thankyou that worked exactly and should shorten the code considerably.
I've never used indirect before, but will look this function up as it seems
to be a very useful tool within excel.
Thanks once again.
Rolohound

> If none of your sheet names contains any space character:
> =VLOOKUP(C15,INDIRECT(A2&"!A1:C50"),2)
[quoted text clipped - 13 lines]
> > then Vlookup(C15,LineA1:C50,2) etc
> > Many Thanks

Reply to this Message

Stephen - 20 Dec 2007 14:52 GMT

You may wish to look here for a good description of how to use INDIRECT:
http://www.cpearson.com/excel/indirect.htm

> Thankyou that worked exactly and should shorten the code considerably.
> I've never used indirect before, but will look this function up as it
[quoted text clipped - 20 lines]
>> > then Vlookup(C15,LineA1:C50,2) etc
>> > Many Thanks

Reply to this Message

Pete_UK - 20 Dec 2007 14:04 GMT

Try it like this:

=VLOOKUP(C15,INDIRECT("'"&A2&"'!A1:C50",2,0)

Hope this helps.

Pete

> I have 10 sheets with different tables on each.
> Using If and Vlookup I've exceded my 7 references.
[quoted text clipped - 6 lines]
> then Vlookup(C15,LineA1:C50,2) etc
> Many Thanks

Reply to this Message