vlookup function using different worksheets

  • Thread starter Thread starter engmgriff
  • Start date Start date
E

engmgriff

Hi All

I have set up a list and would like to perform a vlookup depending on
what the list is equal to. The list values are all different
worksheets.

For example

If Matt was chosen out of the list I would like to perform a vlookup in
the Matt Worksheet. Or in the same list I would like to do a vlookup
say on Chris but in the Chris Worksheet.

Any help would be appreciated

Regards

Matt
 
One way:

I'll assume by "set up a list" and "what the list is equal to" mean
that you've set up a validation list in a cell and want to base the
search on the value selected. Assume that A1 has the lookup value,
that B1 has the validation list, and that your lookups all take
place on columns A&B of your lookup sheets:

=VLOOKUP(A1,INDIRECT(B1 & "!A:B"),2,FALSE)
 
Do you mean if I have set the list contents to equal the worksheets. I
have not done this. I managed to set the list up and you can see the
options in the list but not link them to anything how can I do this.

Regards

Matt
 
I guess I'm confused as to your setup. Where is your list: on the
worksheet?, in a validation list?, a listbox?

How is the value determined? You said:
If Matt was chosen out of the list I would like to perform a vlookup in
the Matt Worksheet.
Click to expand...

but you say that "the list contents (don't) equal the worksheets."
 
The list I have set up is in a validation list. My objective is to
allow me to pick say 'Matt' from this drop down listbox and perform a
vlookup only in the 'Matt' worksheet. However the vlookup which I will
enter might relate to something else in another worksheet but do not
want this information as I'm only concered with the information in
'Matt' worksheet. But if I wanted the same information say from
'Chris' worksheet I would want to pick this worksheet firstly from the
drop down list box and then do my vlookup.

At the moment my list box only shows me the different worksheet names
but are not linked to them in anyway. This listbox is in a seperate
worksheet.

Regards

Matt
 
Did you try the formula I gave you? There doesn't need to be any
link with the target sheet- that's why it uses INDIRECT().
 
Brilliant this works thank you!

However another question which i'm not sure on but you might be able to
help on.

If 'Matt' was chosen in the list box and the vlookup was used say that
it was telephone numbers. How could a display mulitple telephone
number from the lookup. I.e.

A1 cell equals telephone and B1,B2,B3 cells give a telephone number
which are different but related to cell A1 and how would the 3 numbers
be presented i.e in 3 different cells.

Regards

Matt
 
Back
Top