Formula to parse data correctly without pivot table

[campos1578]

Hey Team,

I am working on identifying 4 groups of people based on demographic data and want to be able to quickly pull locations that match the income range along with the row information as shown below:

Also is it possible to create a radial button to that can show sheet 2 data by clicking?

	A	B	C	D	E	F	G	H	I	J	K
1	Location	Average Income	City	State	Average Rent	Average Mortgate		Customer Segment $0-$49,000	Additional row information populated	Customer Segment $50,000-$99,999	Additional row information populated
2	1​	$108,634​	City1	State1	AR1	AM1		=all locations with income range above
3	2​	$64,476​	City2	State2	AR2	AM2
4	3​	$69,827​	City3	State3	AR3	AM3
5	4​	$71,567​	City4	State4	AR4	AM4
6	5​	$78,763​	City5	State5	AR5	AM5
7	7​	$72,613​	City6	State6	AR6	AM6
8	8​	$64,444​	City7	State7	AR7	AM7
9	9​	$58,744​	City8	State8	AR8	AM8
10	10​	$55,995​	City9	State9	AR9	AM9
11	11​	$59,230​	City10	State10	AR10	AM10	Sheet 2 ->				More segments ->
12	12​	$77,989​	City11	State11	AR11	AM11
13	13​	$61,778​	City12	State12	AR12	AM12
14	14​	$60,383​	City13	State13	AR13	AM13
15	15​	$55,867​	City14	State14	AR14	AM14
16	16​	$57,992​	City15	State15	AR15	AM15
17	17​	$60,678​	City16	State16	AR16	AM16
18	18​	$51,879​	City17	State17	AR17	AM17
19	19​	$54,253​	City18	State18	AR18	AM18
20	20​	$66,047​	City19	State19	AR19	AM19
21	21​	$68,186​	City20	State20	AR20	AM20
22	22​	$63,863​	City21	State21	AR21	AM21
23	23​	$62,101​	City22	State22	AR22	AM22
24	24​	$175,000​	City23	State23	AR23	AM23

 

[Fluff]

along with the row information as shown below

You haven't actually shown any expected results, you just have a column called "Additional row information populated" which doesn't tell us much.

 

[campos1578]

Understood and below should bring clarity:
Sheet 1

	A	B	C	D	E	F
1​	Location	Average Income	City	State	Average Rent	Average Mortgate
2​	1​	$45,231​	City1	State1	AR1	AM1
3​	2​	$108,634​	City2	State2	AR2	AM2
4​	3​	$69,827​	City3	State3	AR3	AM3
5​	4​	$71,567​	City4	State4	AR4	AM4
6​	5​	$78,763​	City5	State5	AR5	AM5
7​	7​	$72,613​	City6	State6	AR6	AM6
8​	8​	$64,444​	City7	State7	AR7	AM7
9​	9​	$58,744​	City8	State8	AR8	AM8
10​	10​	$55,995​	City9	State9	AR9	AM9
11​	11​	$59,230​	City10	State10	AR10	AM10
12​	12​	$77,989​	City11	State11	AR11	AM11
13​	13​	$61,778​	City12	State12	AR12	AM12
14​	14​	$60,383​	City13	State13	AR13	AM13
15​	15​	$55,867​	City14	State14	AR14	AM14
16​	16​	$57,992​	City15	State15	AR15	AM15
17​	17​	$60,678​	City16	State16	AR16	AM16
18​	18​	$51,879​	City17	State17	AR17	AM17
19​	19​	$54,253​	City18	State18	AR18	AM18
20​	20​	$66,047​	City19	State19	AR19	AM19
21​	21​	$68,186​	City20	State20	AR20	AM20
22​	22​	$63,863​	City21	State21	AR21	AM21
23​	23​	$62,101​	City22	State22	AR22	AM22
24​	24​	$175,000​	City23	State23	AR23	AM23

Sheet 2

	A	B	C	D	E	F
1			Customer Segment 1 ($0-$49,000)
2	Location	Average Income	City	State	Average Rent	Average Mortgate
3	1​	$45,231​	City1	State1	AR1	AM1
4
5
6
7
8
9
10
11
12
13
14

G	H	I	J	K	L
Customer Segment 2 ($50,000-$99,999)
Location	Average Income	City	State	Average Rent	Average Mortgate
3​	$69,827​	City3	State3	AR3	AM3
4​	$71,567​	City4	State4	AR4	AM4
5​	$78,763​	City5	State5	AR5	AM5
7​	$72,613​	City6	State6	AR6	AM6
8​	$64,444​	City7	State7	AR7	AM7
9​	$58,744​	City8	State8	AR8	AM8
10​	$55,995​	City9	State9	AR9	AM9
11​	$59,230​	City10	State10	AR10	AM10
12​	$77,989​	City11	State11	AR11	AM11
13​	$61,778​	City12	State12	AR12	AM12
14​	$60,383​	City13	State13	AR13	AM13
15​	$55,867​	City14	State14	AR14	AM14
16​	$57,992​	City15	State15	AR15	AM15
17​	$60,678​	City16	State16	AR16	AM16
18​	$51,879​	City17	State17	AR17	AM17
19​	$54,253​	City18	State18	AR18	AM18
20​	$66,047​	City19	State19	AR19	AM19
21​	$68,186​	City20	State20	AR20	AM20
22​	$63,863​	City21	State21	AR21	AM21
23​	$62,101​	City22	State22	AR22	AM22

Does that help?

 

[Sufiyan97]

Try

Book5
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W
1	1	Location	Average Income	City	State	Average Rent	Average Mortgate		0-49000								50,000-99,999
2	2	1	45,231	City1	State1	AR1	AM1		2	1	45,231	City1	State1	AR1	AM1		4	3	69827	City3	State3	AR3	AM3
3	3	2	108,634	City2	State2	AR2	AM2										5	4	71567	City4	State4	AR4	AM4
4	4	3	69,827	City3	State3	AR3	AM3										6	5	78763	City5	State5	AR5	AM5
5	5	4	71,567	City4	State4	AR4	AM4										7	7	72613	City6	State6	AR6	AM6
6	6	5	78,763	City5	State5	AR5	AM5										8	8	64444	City7	State7	AR7	AM7
7	7	7	72,613	City6	State6	AR6	AM6										9	9	58744	City8	State8	AR8	AM8
8	8	8	64,444	City7	State7	AR7	AM7										10	10	55995	City9	State9	AR9	AM9
9	9	9	58,744	City8	State8	AR8	AM8										11	11	59230	City10	State10	AR10	AM10
10	10	10	55,995	City9	State9	AR9	AM9										12	12	77989	City11	State11	AR11	AM11
11	11	11	59,230	City10	State10	AR10	AM10										13	13	61778	City12	State12	AR12	AM12
12	12	12	77,989	City11	State11	AR11	AM11										14	14	60383	City13	State13	AR13	AM13
13	13	13	61,778	City12	State12	AR12	AM12										15	15	55867	City14	State14	AR14	AM14
14	14	14	60,383	City13	State13	AR13	AM13										16	16	57992	City15	State15	AR15	AM15
15	15	15	55,867	City14	State14	AR14	AM14										17	17	60678	City16	State16	AR16	AM16
16	16	16	57,992	City15	State15	AR15	AM15										18	18	51879	City17	State17	AR17	AM17
17	17	17	60,678	City16	State16	AR16	AM16										19	19	54253	City18	State18	AR18	AM18
18	18	18	51,879	City17	State17	AR17	AM17										20	20	66047	City19	State19	AR19	AM19
19	19	19	54,253	City18	State18	AR18	AM18										21	21	68186	City20	State20	AR20	AM20
20	20	20	66,047	City19	State19	AR19	AM19										22	22	63863	City21	State21	AR21	AM21
21	21	21	68,186	City20	State20	AR20	AM20										23	23	62101	City22	State22	AR22	AM22
22	22	22	63,863	City21	State21	AR21	AM21
23	23	23	62,101	City22	State22	AR22	AM22
24	24	24	175,000	City23	State23	AR23	AM23
25
Sheet2

Cell Formulas
Range	Formula
I2:O2	I2	=FILTER(A2:G24,C2:C24<=49000)
Q2:W21	Q2	=FILTER(A2:G24,(C2:C24>=49001)*(C2:C24<=99999))
Dynamic array formulas.

 

[Fluff]

Thanks fr that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer Segment 1 ($0-$49,000)						Customer Segment 2 ($50,000-$99,999)
2	Location	Average Income	City	State	Average Rent	Average Mortgate	Location	Average Income	City	State	Average Rent	Average Mortgate
3	1	45231	City1	State1	AR1	AM1	3	69827	City3	State3	AR3	AM3
4							4	71567	City4	State4	AR4	AM4
5							5	78763	City5	State5	AR5	AM5
6							7	72613	City6	State6	AR6	AM6
7							8	64444	City7	State7	AR7	AM7
8							9	58744	City8	State8	AR8	AM8
9							10	55995	City9	State9	AR9	AM9
10							11	59230	City10	State10	AR10	AM10
11							12	77989	City11	State11	AR11	AM11
12							13	61778	City12	State12	AR12	AM12
13							14	60383	City13	State13	AR13	AM13
14							15	55867	City14	State14	AR14	AM14
15							16	57992	City15	State15	AR15	AM15
16							17	60678	City16	State16	AR16	AM16
17							18	51879	City17	State17	AR17	AM17
18							19	54253	City18	State18	AR18	AM18
19							20	66047	City19	State19	AR19	AM19
20							21	68186	City20	State20	AR20	AM20
21							22	63863	City21	State21	AR21	AM21
22							23	62101	City22	State22	AR22	AM22
23
Sheet2

Cell Formulas
Range	Formula
A3:F3	A3	=FILTER(Sheet1!A2:F24,Sheet1!B2:B24<50000)
G3:L22	G3	=FILTER(Sheet1!A2:F24,(Sheet1!B2:B24>=50000)*(Sheet1!B2:B24<100000))
Dynamic array formulas.

 

[campos1578]

Thanks fr that.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Customer Segment 1 ($0-$49,000)						Customer Segment 2 ($50,000-$99,999)
2	Location	Average Income	City	State	Average Rent	Average Mortgate	Location	Average Income	City	State	Average Rent	Average Mortgate
3	1	45231	City1	State1	AR1	AM1	3	69827	City3	State3	AR3	AM3
4							4	71567	City4	State4	AR4	AM4
5							5	78763	City5	State5	AR5	AM5
6							7	72613	City6	State6	AR6	AM6
7							8	64444	City7	State7	AR7	AM7
8							9	58744	City8	State8	AR8	AM8
9							10	55995	City9	State9	AR9	AM9
10							11	59230	City10	State10	AR10	AM10
11							12	77989	City11	State11	AR11	AM11
12							13	61778	City12	State12	AR12	AM12
13							14	60383	City13	State13	AR13	AM13
14							15	55867	City14	State14	AR14	AM14
15							16	57992	City15	State15	AR15	AM15
16							17	60678	City16	State16	AR16	AM16
17							18	51879	City17	State17	AR17	AM17
18							19	54253	City18	State18	AR18	AM18
19							20	66047	City19	State19	AR19	AM19
20							21	68186	City20	State20	AR20	AM20
21							22	63863	City21	State21	AR21	AM21
22							23	62101	City22	State22	AR22	AM22
23
Sheet2

Cell Formulas
Range	Formula
A3:F3	A3	=FILTER(Sheet1!A2:F24,Sheet1!B2:B24<50000)
G3:L22	G3	=FILTER(Sheet1!A2:F24,(Sheet1!B2:B24>=50000)*(Sheet1!B2:B24<100000))
Dynamic array formulas.

You guys are seriously amazing! Thank you

 

[Fluff]

Glad we could help & thanks for the feedback.