How to Autogenerate Text Assigned to a Number (Not spell it out)

[bluevelvet113]

Hello everyone,

I have an Excel document which lists anonymous participants for a case study. Each participant has been assigned an ID number (e.g. ID: 1). The ID numbers are listed in Column A. The corresponding columns each provide demographic information (e.g. gender, race) and the participants' education background (e.g. college graduate) but instead of these terms being written out, they have been assigned a code. For example, 1=female, 0=male, 8=High School graduate, 9=College graduate.

I would like to have the codes retroactively populate with the corresponding demographic information. For example, change all '1' to read 'female', '8' to read 'High School graduate'. The challenge with this, however, is that the numerical codes can have a different text equivalents depending on the 'demographic information' in each column. For example, in the Gender column (Column B) '1'=female, yet in the Native Language ('Natlang') column (Column C), '1'=English.

I've attached a screenshot for reference. Please let me know if there is a way to do this. By the way, I'm also a basic Excel user so an easy tutorial would be helpful if possible! Thank you!

 

[Habtest]

Formula option could be INDEX(MATCH()), or simply VLOOKUP() if column sequence in the same among input, result and code tables.

Book1
	A	B	C	D	E	F	G	H	I	J
1	INPUT						CODE TABLE
2	#	Gender	Major	Natlang			Code	Gender	Major	Natlang
3	1	0	2	2			0	Male	Econ	English
4	2	1	0	3			1	Female	Acct	Spanish
5	3	0	1	0			2		Phil	French
6	4	1	1	1			3			German
7							4
8	RESULT						5
9	#	Gender	Major	Natlang
10	1	Male	Phil	French
11	2	Female	Econ	German
12	3	Male	Acct	English
13	4	Female	Acct	Spanish
Sheet1

Cell Formulas
Range	Formula
B10:D11	B10	=INDEX($H$3:$J$8,MATCH(B3,$G$3:$G$8,0),MATCH(B$9,$H$2:$J$2,0))
B12:D13	B12	=VLOOKUP(B5,$G:$J,COLUMN(),0)

 

[jasonb75]

I've read the question as being that the codes should be replaced in their original locations rather than trying to reproduce the whole table in a new location, although that would be easier, and less error prone with the INDEX and MATCH version.

To actually replace the codes in situ would either need custom formats (not recommended for anything on this scale), or find and replace (ctrl H), one column at a time, matching 'Entire cell contents' in order to prevent incorrect changes.

 

[bluevelvet113]

Formula option could be INDEX(MATCH()), or simply VLOOKUP() if column sequence in the same among input, result and code tables.

Book1
	A	B	C	D	E	F	G	H	I	J
1	INPUT						CODE TABLE
2	#	Gender	Major	Natlang			Code	Gender	Major	Natlang
3	1	0	2	2			0	Male	Econ	English
4	2	1	0	3			1	Female	Acct	Spanish
5	3	0	1	0			2		Phil	French
6	4	1	1	1			3			German
7							4
8	RESULT						5
9	#	Gender	Major	Natlang
10	1	Male	Phil	French
11	2	Female	Econ	German
12	3	Male	Acct	English
13	4	Female	Acct	Spanish
Sheet1

Cell Formulas
Range	Formula
B10:D11	B10	=INDEX($H$3:$J$8,MATCH(B3,$G$3:$G$8,0),MATCH(B$9,$H$2:$J$2,0))
B12:D13	B12	=VLOOKUP(B5,$G:$J,COLUMN(),0)

Thank you! This is essentially what I'm trying to do. Do you have any tutorials that you can share on how to do a VLOOKUP for this sort of function?

 

[bluevelvet113]

I've read the question as being that the codes should be replaced in their original locations rather than trying to reproduce the whole table in a new location, although that would be easier, and less error prone with the INDEX and MATCH version.

To actually replace the codes in situ would either need custom formats (not recommended for anything on this scale), or find and replace (ctrl H), one column at a time, matching 'Entire cell contents' in order to prevent incorrect changes.

Thanks for your help. I'm essentially trying to do what the user 'Habtest' suggested in the thread. Do you think VLOOKUP would work for this sort of task or is
INDEX MATCH better? Thanks!

 

[jasonb75]

Do you think VLOOKUP would work for this sort of task

It will work under extremely strict conditions, a single change to the layout could easily cause everything to be wrong. The INDEX MATCH method should not fail.

 

[Habtest]

Thank you! This is essentially what I'm trying to do. Do you have any tutorials that you can share on how to do a VLOOKUP for this sort of function?

No problem. Regarding the VLOOKUP, the suggested formula above has a nested COLUMN function inside to determine which catagory in the code table to return, you may find plenty tutorials on these 2 online.

Please note, as Jason and I mentioned, you need to maintain a stricly identical layout, any additional/hidden column will skew the outcome. INDEX(MATCH()) combo is a little more complicated but less hassle especially if the table is to be maintained in the long run.

 

[jasonb75]

Two more methods using Office 365 functions. The XLOOKUP method works the same as the VLOOKUP method suggested by @Habtest, but with a slightly simpler formula that makes some use of dynamic arrays allowing the formula to spill down, but not right.
Another way that would make the formula spill right as well would be to make a slight change to @Habtest's INDEX formula,

=INDEX(H3:J8,MATCH(B3:D6,G3:G8,0),MATCH(B9:D9,H2:J2,0))

Which I believe should also function correctly if the workbook is opened in an older version of excel, although setting up this method in an older version would not be so easy.

Book2
	A	B	C	D	E	F	G	H	I	J
1	INPUT						CODE TABLE
2	#	Gender	Major	Natlang			Code	Gender	Major	Natlang
3	1	0	2	2			0	Male	Econ	English
4	2	1	0	3			1	Female	Acct	Spanish
5	3	0	1	0			2		Phil	French
6	4	1	1	1			3			German
7							4
8	RESULT						5
9	#	Gender	Major	Natlang
10	1	Male	Phil	French
11	2	Female	Econ	German
12	3	Male	Acct	English
13	4	Female	Acct	Spanish
14
15	RESULT
16	#	Gender	Major	Natlang
17	1	Male	Phil	French
18	2	Female	Econ	German
19	3	Male	Acct	English
20	4	Female	Acct	Spanish
Sheet2

Cell Formulas
Range	Formula
B10:D13	B10	=FILTER(FILTER($H$3:$J$8,$G$3:$G$8=B3),$H$2:$J$2=B$9,"")
B17:D20	B17	=XLOOKUP(B3:B6,$G3:$G8,H3:H8)
Dynamic array formulas.

 

[bluevelvet113]

No problem. Regarding the VLOOKUP, the suggested formula above has a nested COLUMN function inside to determine which catagory in the code table to return, you may find plenty tutorials on these 2 online.

Please note, as Jason and I mentioned, you need to maintain a stricly identical layout, any additional/hidden column will skew the outcome. INDEX(MATCH()) combo is a little more complicated but less hassle especially if the table is to be maintained in the long run.

Thanks! That's good to keep in mind.