VLLOKUP formulas

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

Look in HELP for the INDIRECT() function

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"mr_teacher" <[email protected]> wrote in message
news:[email protected]...
|
| 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...o&userid=34352
| View this thread: http://www.excelforum.com/showthread...hreadid=564849
|

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" wrote:

>
> 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...o&userid=34352
> View this thread: http://www.excelforum.com/showthread...hreadid=564849
>
>

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

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!!!

Carl