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
Column A	Value	Extract	Extract2
T12	Metropolitan	Metropolitan
T8	Micropolitan	Micropolitan
T12	Small Town	Small Town
T12	Urban	Urban
Urban
Metropolitan
T12		Urban
T8		Metropolitan

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]4[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]#VALUE![/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

[TD="align: right"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=IFERROR(IF(OR(A2={"T12","T8"}),CHOOSE(B2,"Metropolitan","Micropolitan","Small Town","Urban"),""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]D2[/TH]
[TD="align: left"]{=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"),"")}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

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!