• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Assign code

kbhush

New Member
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?
 
Do this...


In your code mapping table, type this formula in 5th column

(assumes data is in Sheet2!A1:D17)


=B2&C2&D2


Now, drag the formula to fill all the way up to E17.


In your main data (student list), in the column where you want the code, write,


=INDEX(sheet2!$A$1:$A$17,MATCH(B2&C2&D2,sheet2!$E$1:$E$17,0))


Refer to http://chandoo.org/wp/2010/11/02/multi-condition-lookup/ for some help on these formulas
 
Back
Top