How do i lengthen an existing VLOOKUP table array?
 

Assume I have 400 pupils and an array that links numeric grades to a letter;
e. g., 100 = A, 90 = B, etc. How do I increase the array if I want to go
100, 95, 90, 85, 80, etc (in order to add plus or minus to the letter)?
(This is not my exact problem, but I hoped a simple example would make it
clearer.). . . Do I delete the old array and create a new one with the same
name? And where can I find instructions on how to modify, delete and add
such an array? Thanks.

The easiest way I know to do this is to simply type in the new array values,
then re-define the array name range (or just update the cells used in
VLOOKUP). An alternative would be to insert rows / columns in the middle of
the array, so Excel will automatically expand the named array or VLOOKUP cell
range for you.

I haven't a link to instructions for you, but I can tell you this:
To add an array, select the cells in the array. Open the Insert menu, then
select Name and Define. Type in the array name and click, "Add." This can
be used to overwrite an existing array as well (i.e. modify).

To delete an array, go to Insert Name Define again, select the name, and
click, "Delete." Note that this does not erase the values in the array.

I hope this answers your question. Let me know if it does not.

Nate

"artesia7" wrote:

Assume I have 400 pupils and an array that links numeric grades to a letter;
e. g., 100 = A, 90 = B, etc. How do I increase the array if I want to go
100, 95, 90, 85, 80, etc (in order to add plus or minus to the letter)?
(This is not my exact problem, but I hoped a simple example would make it
clearer.). . . Do I delete the old array and create a new one with the same
name? And where can I find instructions on how to modify, delete and add
such an array? Thanks.

Perhaps an easy alternative to consider, instead of getting into defined
ranges, is to simply use cols A and B in a sheet for the table array

E.g. if we set up the table array in cols A & B (Points in col A - Letters
in col B, in ascending order by points in col A) in a specific sheet, say
Sheet2, then we could use in say, Sheet1's B1: =VLOOKUP(A1,Sheet2!A:B,2)

And maintaining / lengthening the table array in Sheet2
would no longer pose any problems to the VLOOKUP formulas in Sheet1 since
entire col references are used for the table array (these references need
not be touched)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Hi

Use dynamic range.

P.e. you have the grade table (GradeNum,GradeText) on sheet Grades - with
headers in cells A1:B1. Define a named range (InsertNameDefine) Grades
with source
=OFFSET(Grades!$A$1,1,,COUNTA(Grades!$A:$A)-1,2)
So long as you don't have any gaps in column A on sheet Grades, and don't
delete the header row, the named range adjusts automatically whenever you
add a row or delete one

Now you can write your VLOOKUP formula like this
=VLOOKUP(LookupValue,Grades,2,0)


--
Arvi Laanemets
( My real mail address: arvil<attarkon.ee )



"artesia7" wrote in message
...
Assume I have 400 pupils and an array that links numeric grades to a
letter;
e. g., 100 = A, 90 = B, etc. How do I increase the array if I want to go
100, 95, 90, 85, 80, etc (in order to add plus or minus to the letter)?
(This is not my exact problem, but I hoped a simple example would make it
clearer.). . . Do I delete the old array and create a new one with the
same
name? And where can I find instructions on how to modify, delete and add
such an array? Thanks.

If you are on Excel 2003, select the current table area including the
labels, activate Data|List|Create List, and keep the current VLOOKUP
formulas as is. When you add new records to the list just created, the
formulas will automatically adjust the range refernces.

artesia7 wrote:
Assume I have 400 pupils and an array that links numeric grades to a letter;
e. g., 100 = A, 90 = B, etc. How do I increase the array if I want to go
100, 95, 90, 85, 80, etc (in order to add plus or minus to the letter)?
(This is not my exact problem, but I hoped a simple example would make it
clearer.). . . Do I delete the old array and create a new one with the same
name? And where can I find instructions on how to modify, delete and add
such an array? Thanks.

How do i lengthen an existing VLOOKUP table array?
 

Thanks, Aladin. It works just as you indicated.

"Aladin Akyurek" wrote:

If you are on Excel 2003, select the current table area including the
labels, activate Data|List|Create List, and keep the current VLOOKUP
formulas as is. When you add new records to the list just created, the
formulas will automatically adjust the range refernces.