VLLOKUP formulas

[mr_teacher]

Hi,

Anyone have any ideas if it is possible t find the range to look up in
a VLOOKUP formula by referencing a cell?

For example if I used the formula

=vlookup(a1,d1,1,false)

Where d1 would contain a value that is a named range such as Numeracy
etc that would refer to a spreadsheet elsewhere in the workbook?

Thanks for any help

Carl

 

[Niek Otten]

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

|
| Hi,
|
| Anyone have any ideas if it is possible t find the range to look up in
| a VLOOKUP formula by referencing a cell?
|
| For example if I used the formula
|
| =vlookup(a1,d1,1,false)
|
| Where d1 would contain a value that is a named range such as Numeracy
| etc that would refer to a spreadsheet elsewhere in the workbook?
|
| Thanks for any help
|
| Carl
|
|
| --
| mr_teacher
| ------------------------------------------------------------------------
| mr_teacher's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=34352
| View this thread: http://www.excelforum.com/showthread.php?threadid=564849
|

 

[Guest]

Yes, it's possible. Use the INDIRECT function with the named range listed in
D1, as follows:

=VLOOKUP(A1,INDIRECT(D1),2,FALSE)

For example, D1 might contain the word TEST. TEST is the name assigned to
the range B10:C30.

Hope this helps,

Hutch

 

[mr_teacher]

Ok, had a look at the help for that and I think I can get the general
gist of it so will have a play around with it.

Can this be used to reference data though that is on a different
worksheet? I basically have 36 different small spreadsheets in various
places within the same workbook and I want to be able to call on data
from any of these 36 tables and reference them in one table that will
display the results?

Ideally I would be able to reference the named range in my results
spreadsheet (through drop down selection lists to choose the correct
data)and this would call up and display the data from the source
table.

Not sure if that is making any sense now or not though! Hope it does

Carl

 

[mr_teacher]

Ah read that through again at a slow speed and it all makes sense!!!

Tried it out and works like a dream!

Solved loads of problems for me there!

Thanks a lot for all the help!!!

Car