# Values represented by String

#### spsafe

##### New Member
Dear all,

I have a column (A) with numerical values ranging from 1-100 and I want to create another column (B) which will have respective labels for different ranges of the values in column (A). For example, I want values in column A between 51-60 to take the label "Alpha" in column (B) and values between 61-70 to take the label "Beta", etc.

Pavlos

### Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

#### dafan

##### Well-known Member
Make a table somewhere in your workbook with 1, 11, 21 etc.
Use a VLOOKUP to look up the value (eg 54):
=VLOOKUP(A1,Sheet2!A1:B3,2,1)

In this case I made in sheet2 three cells in column A {1,11,21}, and 3 cells in column B {Alpha, Beta, Gamma}.
In sheet1 I put the formula above in B1, the value to look up in A1.

6 -> Alpha,
14 -> Beta
etc

HTH.

#### BGY23

##### Well-known Member
Create a lookup table with the values in one column I.e. 1 to 100 and in the right hand adjacent column put you description i.e. Alpha etc

Now on your original list do a vlookup based on the table you just created.

You can update your lookup table with changes in descriptions and it will flow through to your data automatically.

New descriptions can be added and agian it will update your report.

Alternatively if you only have a few names you can write an if statement for ranges i.e.

Assume your data starts at A1

If(and(A51=>1,A1=<60),"Alpha",If(and(A61=>1,A1=<70),"Beta",0))

Problem is you can only have 7 options in a nested if statement. Any changes will mean you have to rewrite the whole formula.

Good luck

#### BGY23

##### Well-known Member
****ed the formula up sorry..........

If(and(A1=>51,A1=<60),"Alpha",If(and(A1=>61,A1=<70),"Beta",0))

#### Mike Blackman

##### Well-known Member
Hi,

No need for a Lookup table on this, ammend this with all the buckets you wish to create, I have added one for 60's and 70's;

=LOOKUP(A1,{0,"Other";60,"Alpha";70,"Beta";80,"Other"})

#### spsafe

##### New Member
Thank you all guys.

I have followed BGY23's suggestion and it works fine. I am pretty sure that should I use any of the other suggestions I would also be successful.

Best,

Pavlos

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,167,652
Messages
5,854,960
Members
431,689
Latest member
jacker01

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back