if then formula for alphabetizing

**ValerieC** · 05-07-2009, 02:14 PM

I have a column with 1 letter in it. I need to write a formula that will look at each cell in that column and assign a numeric value depending on the letter of the alphabet. A & B would be 1, c & d would be 2, e & F would be 3 and so forth up to 10. I used a nested if then, but after about G it stopped working - I believe because it was too nested. The formula is below (I added brackets around each result). Any ideas on how to nest for all 26 letters?

Thanks.

=IF(C3="a","[1]", IF(C3="b","[1]", IF(C3="c","[2]", IF(C3="d","[2]", IF(C3="e","[3]", IF(C3="f","[3]", IF(C3="g","[4]",300)))))))

**Paul** · 05-07-2009, 02:18 PM

Hi Valerie, and welcome to the forum.

Instead of using IF statements, you could use LOOKUP. For example:

=LOOKUP(A1,{"A","B";"C","D";"E","F"},{1,2,3})

The commas in the middle array, { }, separate values that are similar, and semi-colons separate groups. So A or B return 1, C or D return 2, E or F return 3, etc.. expand up to Z.

if then formula for alphabetizing

LinkBack

Thread Tools

Rate This Thread

Display

if then formula for alphabetizing

Re: if then formula for alphabetizing

Thread Information

Users Browsing this Thread

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions