Assign Numbers Based on Alphabet

cjdecarr

New member
Joined
Aug 25, 2016
Messages
1
Reaction score
0
Points
0
Hello All,

I am trying to create a sheet that will automatically assign people to a room based on their last name. I think that I am overthinking this and that it is a simple formula, but I can not figure it out. The names are in column a and in column b I want it to automatically assign the room based on their last name. Everyone with the last name that starts with A-BL goes in room 1, Last names starting with Bo-BZ go to room 2, ect. Here is an example of what I am trying to do.

NameRoom
Allen, Robert1
Bisson, Lucas2
Ben, Tyler1


Any help would be greatly appreciated. Thank you.
 
You're asking for a solution without supplying enough information.

You'll need to either supply ect. for the rest of the alphabet
OR
supply what determined that 1 went A-BL and 2 goes Bo-BZ and how that should be applied to the rest of the alphabet.

What happens with BM and BN ?
 
Last edited:
Build/use a lookup table using the first two letters as the index.

Use a formula like this to find the room number:

Code:
=VLOOKUP(LEFT(E2,2),$A$1:$B$6,2,1)

Where E2 is the last name, and A1:B6 is the lookup table.

Cheers,
 
It has to be said that even the small set of sample data does not match what is being requested: why is Lucas Bisson, whose name comes in the alphabet before Bl, going into room 2? Surely he should be allocated to room 1?
 
Back
Top