Populating a Column Based Off of Two Columns Values

[MDean3313]

Hi Everyone,

I've posted a similar question, but am having new troubles.

One column has 4 values, T12, T8, empty cells, and #VALUE !. The second column has values 1 through 4.

The new column needs to take the value from the second column (numbers 1-4) and match it to a title. For example, if a cells value is 1, the new column would say "Metropolitan", if it's 2, the new column would say "Micropolitan", if 3, the new column would say "Small Town" and 4 would be "Urban."

Additionally, I want to only populate the new column if it correlates with "T12" or "T8" and to leave blank if there is an empty cell or a #VALUE ! from the other column.

Let me know if you have any questions and thanks for the help!

 

[Eric W]

Is one of these what you want?

Book1
	A	B	C	D	E	F
1	Column A	Value		Extract		Extract2
2	T12	1		Metropolitan		Metropolitan
3	T8	2		Micropolitan		Micropolitan
4	T12	3		Small Town		Small Town
5	T12	4		Urban		Urban
6		1		Urban
7		2		Metropolitan
8	#VALUE!	3
9	T12	4				Urban
10	#VALUE!	1
11	T8	1				Metropolitan
12
Sheet1

Cell Formulas
Range	Formula
F2	=IFERROR(IF(OR(A2={"T12","T8"}),CHOOSE(B2,"Metropolitan","Micropolitan","Small Town","Urban"),""),"")
D2	{=IFERROR(CHOOSE(INDEX($B$2:$B$20,SMALL(IF(NOT(ISERROR($A$2:$A$20)),IF(LEFT($A$2:$A$20)="T",ROW($A$2:$A$20)-ROW($A$2)+1)),ROWS($D$2:$D2))),"Metropolitan","Micropolitan","Small Town","Urban"),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Eric W]

Slightly shorter versions of both formulas:

Book1
	A	B	C	D	E	F
1	Column A	Value		Extract		Extract2
2	T12	1		Metropolitan		Metropolitan
3	T8	2		Micropolitan		Micropolitan
4	T12	3		Small Town		Small Town
5	T12	4		Urban		Urban
6		1		Urban
7		2		Metropolitan
8	#VALUE!	3
9	T12	4				Urban
10	#VALUE!	1
11	T8	1				Metropolitan
Sheet1

Cell Formulas
Range	Formula
D2	=IFERROR(CHOOSE(INDEX($B$2:$B$20,AGGREGATE(15,6,(ROW($B$2:$B$20)-ROW($B$2)+1)/(LEFT($A$2:$A$20)="T"),ROWS($D$2:$D2))),"Metropolitan","Micropolitan","Small Town","Urban"),"")
F2	=IF(ISERROR(RIGHT(A2)+0),"",CHOOSE(B2,"Metropolitan","Micropolitan","Small Town","Urban"))

 

[MDean3313]

Is one of these what you want?

	A	B	C	D	E	F
1	Column A	Value		Extract		Extract2
2	T12	1		Metropolitan		Metropolitan
3	T8	2		Micropolitan		Micropolitan
4	T12	3		Small Town		Small Town
5	T12	4		Urban		Urban
6		1		Urban
7		2		Metropolitan
8	#VALUE!	3
9	T12	4				Urban
10	#VALUE!	1
11	T8	1				Metropolitan
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula F2 =IFERROR(IF(OR(A2={"T12","T8"}),CHOOSE(B2,"Metropolitan","Micropolitan","Small Town","Urban"),""),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Array Formulas Cell Formula D2 {=IFERROR(CHOOSE(INDEX($B$2:$B$20,SMALL(IF(NOT(ISERROR($A$2:$A$20)),IF(LEFT($A$2:$A$20)="T",ROW($A$2:$A$20)-ROW($A$2)+1)),ROWS($D$2:$D2))),"Metropolitan","Micropolitan","Small Town","Urban"),"")} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thank you for this Eric!
The Worksheet Formula was able to solve my problem.
I really appreciate the help!

 

[Eric W]

Glad I could help!