LookUp Problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

LostNFound - 13 Oct 2006 05:01 GMT

I need to look at a single cell and it will contain a value from 1-1000, I
want to return different letters based on the cell value. Using ranges for
different letters>>

Example - 1-100 will return an A, 101-150 will return a B, etc. all the way
to 1000 in increments of 50.

The column C will contain the a number between 1 and 1000 and I want to
return a letter based on the example above in a different column.

Reply to this Message

Max - 13 Oct 2006 05:59 GMT

One way ..

Assuming lookup values entered in A1 down,

Put in say, B1:
=IF(A1<1,"",VLOOKUP(A1,{1,"A";101,"B";151,"C";201,"D";251,"E";301,"F";351,"G";401,"H";451,"I";501,"J";551,"K";601,"L";651,"M";701,"N";751,"O";801,"P";851,"Q";901,"R";951,"S";1001,"T"},2))
Copy down

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

>I need to look at a single cell and it will contain a value from 1-1000, I
> want to return different letters based on the cell value. Using ranges for
[quoted text clipped - 6 lines]
> The column C will contain the a number between 1 and 1000 and I want to
> return a letter based on the example above in a different column.

Reply to this Message

Biff - 13 Oct 2006 06:18 GMT

Try this:

=IF(OR(C1="",C1>1000),"",IF(C1<101,"A",CHAR(64+CEILING(C1,50)/50-1)))

Biff

Reply to this Message