I have a list of students as shown below.
NAME SEX CATEGOR AREA
AAINA SHARMA GIRL GEN RURAL
ABHEMANU PAREEK BOY ST RURAL
AJEET PRAJAPAT BOY OBC URBAN
AKASH SHARMA BOY GEN URBAN
AKILESH SHARMA BOY GEN RURAL
AMBIKA BENIWAL GIRL OBC RURAL
AMIT MATVA BOY OBC RURAL
ANIL BOY OBC URBAN
ANIL CHAWALA BOY SC URBAN
ANIL KUMAR BOY OBC RURAL
ANITA GIRL OBC RURAL
ANITA KUMARI GIRL SC RURAL
ANJANA PRAJAPAT GIRL OBC URBAN
ANKITA PAREEK GIRL GEN RURAL
ANUJ KUMAR BOY SC RURAL
APESHA KAWAR GIRL OBC URBAN
ARADHANA GIRL ST RURAL
ASHOKA KUMARI GIRL OBC RURAL
BABULAL BOY ST RURAL
BAJRANG LAL BOY OBC RURAL
I want to assign code to each student in above list as per the code list shown below. Eg. Record 1 is GIRL, category is GEN and area is RURAL so she should get code M. Similarly 2nd record should get L and so on.
CODE SEX CATEGORY AREA
A BOY GEN URBAN
B BOY OBC URBAN
C BOY SC URBAN
D BOY ST URBAN
E GIRL GEN URBAN
F GIRL OBC URBAN
G GIRL SC URBAN
H GIRL ST URBAN
I BOY GEN RURAL
J BOY OBC RURAL
K BOY SC RURAL
L BOY ST RURAL
M GIRL GEN RURAL
N GIRL OBC RURAL
O GIRL SC RURAL
P GIRL ST RURAL
I tried to use lookup function but could not succeed. Can someone help me to build a formula?
NAME SEX CATEGOR AREA
AAINA SHARMA GIRL GEN RURAL
ABHEMANU PAREEK BOY ST RURAL
AJEET PRAJAPAT BOY OBC URBAN
AKASH SHARMA BOY GEN URBAN
AKILESH SHARMA BOY GEN RURAL
AMBIKA BENIWAL GIRL OBC RURAL
AMIT MATVA BOY OBC RURAL
ANIL BOY OBC URBAN
ANIL CHAWALA BOY SC URBAN
ANIL KUMAR BOY OBC RURAL
ANITA GIRL OBC RURAL
ANITA KUMARI GIRL SC RURAL
ANJANA PRAJAPAT GIRL OBC URBAN
ANKITA PAREEK GIRL GEN RURAL
ANUJ KUMAR BOY SC RURAL
APESHA KAWAR GIRL OBC URBAN
ARADHANA GIRL ST RURAL
ASHOKA KUMARI GIRL OBC RURAL
BABULAL BOY ST RURAL
BAJRANG LAL BOY OBC RURAL
I want to assign code to each student in above list as per the code list shown below. Eg. Record 1 is GIRL, category is GEN and area is RURAL so she should get code M. Similarly 2nd record should get L and so on.
CODE SEX CATEGORY AREA
A BOY GEN URBAN
B BOY OBC URBAN
C BOY SC URBAN
D BOY ST URBAN
E GIRL GEN URBAN
F GIRL OBC URBAN
G GIRL SC URBAN
H GIRL ST URBAN
I BOY GEN RURAL
J BOY OBC RURAL
K BOY SC RURAL
L BOY ST RURAL
M GIRL GEN RURAL
N GIRL OBC RURAL
O GIRL SC RURAL
P GIRL ST RURAL
I tried to use lookup function but could not succeed. Can someone help me to build a formula?