Entering array in single cell

G

Guest

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.
 
J

JulieD

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"}
Click to expand...

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
 
G

Guest

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
 
G

Guest

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
 
P

Peo Sjoblom

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
 
G

Guest

Excellent, thanks

Peo Sjoblom said:
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
Click to expand...
 
Top