nested if statements - is there an alternative

[luciegiles]

Hi all,

I have an excel file with a number of validated lists that contain text; I would like to save a corresponding numerical value in another column when a particular option is picked from the text list.

for example one column uses a list of Teachers names and each teacher has a corresponding numerical value:

 TeacherName TeacherID 
 TeacherA    11 
 TeacherB    15
 TeacherC    23

I want the user to select TeacherA from the list but in another field have the code 11 be entered. I was using a set of nested if statements to do this which was working but hit a problem once getting to the 7th instance. Is there another way? BTW the numbers are required as the data will ultimately be imported to an access database; the numbers correspond to lookup lists in the database.

thanks
LG

[Rabbit]

Store all the teachers and their id in another sheet. Then use a lookup function to get the id instead of hardcoding it into the function. That way you can add/delete teachers without having to update the formula.

[luciegiles]

Thanks Rabbit,

I used the vlookup function to get the result I wanted.

thanks again

LG