using strings in vlookup function calls


A

AtTheEndofMyRope

Hi.

I am having success using vlookup on a sheet (Week 1).

The vlookup formula is as: =VLOOKUP(B8,'Week 1'!A2:F17,4,FALSE)

It properly returns "Thu" from column 4 of the mentioned array in the
sheet named "Week 1".

B8 sequences up in the array as it is the lookup reference value.

My copied array of that formula also works.

I want to replace 'Week 1' sheet name declaration with a call to a
sheet whose name is stored in a cell like "B2". The cell contents matches
the sheet name exactly And is chosen from a drop down list validation
schema at present.

I have 17 sheets of data, and one sheet to examine them in, and the drop
down list allows choosing a sheet (week), I want that drop down cell
content to be the replacement text in the vlookup formula. That way the
sheet contents reflects the selected week's data.

Also, it would be nice to reference a named array in the "A2:F17" part
of the formula like "Week 1" through "Week 17" in the formula, also based
on the contents selected for B2.

I cannot remember how to declare the string so that excel looks at it
correctly.
 
Ad

Advertisements

P

Pete_UK

Try this:

=VLOOKUP(B8,INDIRECT("'"&B2&"'!A2:F17"),4,FALSE)

Note that if you use named ranges for the tables then spaces are not
allowed - Excel will substitute an underscore for the space. If your
ranges are named uniquely (i.e. you only have one Week_3 for example)
then you will not need to bother with the sheet name. So, if you have
the name Week_3 in B2, and that refers to $A$2:$F$17 on the sheet
named Week3, then you should be able to do it with:

=VLOOKUP(B8,INDIRECT(B2),4,FALSE)

Hope this helps.

Pete
 
A

AtTheEndofMyRope

Thank you. I will attempt each, but I will have to rename my sheets
first. Calling the named array directly does sound better though.

I'll just likely skip to method.

Thanks again.
 
A

AtTheEndofMyRope

Thank you, your solutions worked perfectly.

I now have a new problem.

Vlookup will not carry an image over from a cell contents.

Is there a way to transport the image in a cell using a vlookup to
handle the variable value of the referenced cell?
 
Ad

Advertisements

P

Pete_UK

Thanks for feeding back - glad the formulae worked for you.

Gord has given you the same link that I would have for your problem
with pictures.

Pete
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top