VLOOKUP: Naming a range

As a teacher I have used the VLOOKUP function in Excel often to give
students a grade from their exam marks.

Recently I found a spreadsheet which used 'm2g' instead of AD8:AE16 as the
table_array value.

I know that if I substitute the range instead of the m2g I get the same
answer but am mystified as to why it works.

Could it be that somewhere on the spreadsheet (that I can't find) something
'names' the range m2g?

Mystified,

Noel

The spreadsheet can be seen by clicking on the: Review your Business
Systems portfolio grade using this spreadsheet on the right of the webpage
at:

http://www.ralphallen.bathnes.sch.uk...0ict/index.htm

Hi

To define a named range, select the range, from Edit menu select
Insert>Name>Define, enter the name for selected range, and click on Add
button.
It's not obligatory to select the range at start, you also can enter the
range directly into field RefersTo. When you don't use absolute references,
the defined range depends on active cell/calling cell (and can return an
error, when p.e. the range was defined with cell B2 active, and you select
the sell A1, or refer to it in formula in A1). When the sheet, the range
refers to, isn't defined, the range on active sheet is returned.

Named ranges which depends on data on sheet, or on calling cell address, are
called 'dynamic named ranges'. They are often used to return the datarange,
where rows are added or removed freguently. P.e. the named range with
RefersTo formula (a simple one)
=OFFSET(Sheet1!$A$1,1,,COUNTA($A:$A)-1,3)
, and non-empty column headers in range Sheet1!A1:C1, and without any empty
rows in used range,
returns the range Sheet1!A2:C#, where # is the number of last row in used
range. Whenever the user adds a row, or deletes some, the range is adjusted
automatically.


--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Noel S Pamfree" <[email protected]> wrote in message
news:%[email protected]...
> As a teacher I have used the VLOOKUP function in Excel often to give
> students a grade from their exam marks.
>
> Recently I found a spreadsheet which used 'm2g' instead of AD8:AE16 as the
> table_array value.
>
> I know that if I substitute the range instead of the m2g I get the same
> answer but am mystified as to why it works.
>
> Could it be that somewhere on the spreadsheet (that I can't find)
> something 'names' the range m2g?
>
> Mystified,
>
> Noel
>
> The spreadsheet can be seen by clicking on the: Review your Business
> Systems portfolio grade using this spreadsheet on the right of the
> webpage at:
>
> http://www.ralphallen.bathnes.sch.uk...0ict/index.htm
>
>