Entering array in single cell
 

I want to create an array in a single cell and use that as a lookup. I
thought I'd found exactly what I need on Chip Pearson's site which describes
precisely what I'm trying to do but when I follow the instrucions my lookup
formula results in #REF!

If I try and enter the array with Ctrl-Shft-Enter I get the "the formula you
typed contains an error " message.

I'm confused! I must be missing something really obvious. The info from
Chip Pearson's site is below. I'd be grateful for an idiot's guide on
exactly how to get this to work.

Thanks a lot


----------------
........create a define name called "Grades" which refers to the array:

={0,"F";60,"D";70,"C";80,"B";90,"A"}

Then, use VLOOKUP to convert the number to the grade:

=VLOOKUP(A1,Grades,2)

where A1 is the cell contains the numeric value.

Hi

i'm guessing you missed or misunderstood the
.......create a define name called "Grades" which refers to the array:
={0,"F";60,"D";70,"C";80,"B";90,"A"}

what this means is to choose insert / name / define - in the top line type
Grades
in the refers to box type
={0,"F";60,"D";70,"C";80,"B";90,"A"}
exactly as written (copy & paste) and commit using the enter key - (not
control & shift & enter)


Now try the VLOOKUP statement in a cell, again just using enter to commit.

Cheers
JulieD


"nospaminlich" wrote in message
...
I want to create an array in a single cell and use that as a lookup. I
thought I'd found exactly what I need on Chip Pearson's site which
describes
precisely what I'm trying to do but when I follow the instrucions my
lookup
formula results in #REF!

If I try and enter the array with Ctrl-Shft-Enter I get the "the formula
you
typed contains an error " message.

I'm confused! I must be missing something really obvious. The info from
Chip Pearson's site is below. I'd be grateful for an idiot's guide on
exactly how to get this to work.

Thanks a lot


----------------
.......create a define name called "Grades" which refers to the array:

={0,"F";60,"D";70,"C";80,"B";90,"A"}

Then, use VLOOKUP to convert the number to the grade:

=VLOOKUP(A1,Grades,2)

where A1 is the cell contains the numeric value.

Thanks a lot Julie. That's fixed it.

You were right. I misunderstood that part. I thought you had to enter the
array in a cell and the Define Name referred to that cell but I've got it now.

Thanks again

you're welcome and thanks for the feedback

"nospaminlich" wrote in message
...
Thanks a lot Julie. That's fixed it.

You were right. I misunderstood that part. I thought you had to enter
the
array in a cell and the Define Name referred to that cell but I've got it
now.

Thanks again

Just a thought....

The array here is relatively quick and easy to type in. If you wanted to
use the same principle for a much larger array is it possible to convert info
in a range of say A2:C40 into a format that could be copied into the Define
Name box thus allowing the info in those cells to be deleted? If so, how?

Thanks a lot

use

=YourRange

press enter, select the formula in the formula bar and press F9, copy.
Note that named formulas can only hold I believe 255 characters

--

Regards,

Peo Sjoblom

"nospaminlich" wrote in message
...
Just a thought....

The array here is relatively quick and easy to type in. If you wanted to
use the same principle for a much larger array is it possible to convert
info
in a range of say A2:C40 into a format that could be copied into the
Define
Name box thus allowing the info in those cells to be deleted? If so, how?

Thanks a lot

Excellent, thanks

"Peo Sjoblom" wrote:

use

=YourRange

press enter, select the formula in the formula bar and press F9, copy.
Note that named formulas can only hold I believe 255 characters

--

Regards,

Peo Sjoblom

"nospaminlich" wrote in message
...
Just a thought....

The array here is relatively quick and easy to type in. If you wanted to
use the same principle for a much larger array is it possible to convert
info
in a range of say A2:C40 into a format that could be copied into the
Define
Name box thus allowing the info in those cells to be deleted? If so, how?

Thanks a lot