Reference min and max to determine which segment

[thp510]

Hello. I have a lot of rows of data that indicate a company's employee size from 1 - 100k. We are doing an exercise to determine which segment each of these companies fall under based on their employee size. If it's 1-1000, it's SMB. If it's 1001-5000 it's Commercial. If it's over 5k than it's Enterprise. Rather than doing a nested IF statement on a column that looks at the employee count and then copying the formula all the way down the rows, is there an easier way to do this by just referencing a table on another worksheet? For example, I have created a table like this on a separate worksheet (or off to the side in another column):

	A	B	C
1	Min	Max	Segment
2	1	1000	SMB
3	1001	5000	Commercial
4	5001	No Max	Enterprise

I'm hoping to just reference this table instead to determine what segment the account should be in. This way if I need to change the min and max thresholds for each segment, I don't have to change the formula all the way down the rows and I can just change this table above. Hope that makes sense.

Thanks!

 

[Jeffrey Mahoney]

Setting up a VLOOKUP with an Approximate return like this will give you what you need.

test varieties.xlsm
	B	C	D
1		Max	Segment
2		1	SMB
3		1,000	SMB
4		1001	Commercial
5		5,000	Commercial
6		5,001	Enterprise
7		999,999,999	Enterprise
8
9	500	SMB
10	1200	Commercial
11	10450	Enterprise
Sheet12

Cell Formulas
Range	Formula
C9:C11	C9	=VLOOKUP(B9,$C$2:$D$7,2,TRUE)

 

[Toadstool]

Hi THP510,

I see you have an answer using VLOOKUP, so I'll take the same approach using INDEX and MATCH

THP510-2.xlsx
	A	B	C	D	E	F
1	Min	Segment			Employees	Segment
2	1	SMB			1	SMB
3	1001	Commercial			5	SMB
4	5001	Enterprise			1000	SMB
5					1001	Commercial
6					2000	Commercial
7					5000	Commercial
8					5001	Enterprise
9					9999999	Enterprise
Sheet1

Cell Formulas
Range	Formula
F2:F9	F2	=INDEX($B$2:$B$4,MATCH(E2,$A$2:$A$4,1))

 

[thp510]

Thank you all!