Clustering values

[cogumelo]

Hi,

I have a list of values where i want to use a formula to fill the last column (Q) with the corresponding value q1, q2, q3 or q4 that is defined in the second table.

Can anyone help out with the correct formula to do this?

thx in advance

list
[TABLE="width: 500"]
<tbody>[TR]
[TD]name[/TD]
[TD]value[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]text1[/TD]
[TD]99[/TD]
[TD]q1[/TD]
[/TR]
[TR]
[TD]text2[/TD]
[TD]101[/TD]
[TD]q2[/TD]
[/TR]
[TR]
[TD]text3[/TD]
[TD]200[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]text4[/TD]
[TD]500[/TD]
[TD]...[/TD]
[/TR]
[TR]
[TD]text5[/TD]
[TD]1[/TD]
[TD]...[/TD]
[/TR]
</tbody>[/TABLE]



[TABLE="width: 500"]
<tbody>[TR]
[TD]q1[/TD]
[TD]>0[/TD]
[TD]<100[/TD]
[/TR]
[TR]
[TD]q2[/TD]
[TD]>101[/TD]
[TD]<200[/TD]
[/TR]
[TR]
[TD]q3[/TD]
[TD]>201[/TD]
[TD]<300[/TD]
[/TR]
[TR]
[TD]q4[/TD]
[TD]>301[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

If your second table doesn't change you can use
=LOOKUP(B2,{0,101,201,301},{"q1","q2","q3","q4"})

 

[cogumelo]

If your second table doesn't change you can use
=LOOKUP(B2,{0,101,201,301},{"q1","q2","q3","q4"})

Hi Fluff,

Thanks. It works perfectly but they do change every now and then. Can you show me how you would do it in that case?

thx in advance

 

[Fluff]

How about

Excel 2013 32 bit
	A	B	C
1	name	value
2	text1	99	q1
3	text2	101	q2
4	text3	301	q4
5	text4	500	q4
6	text5	1	q1
7
8
9
10
11	q1	0
12	q2	101
13	q3	201
14	q4	301
Summary 1

Cell Formulas
Range	Formula
C2	=INDEX(A$11:B$14,MATCH(B2,B$11:B$14,1),1)

Where the green cells represent the lookup table

 

[cogumelo]

Thanks for your great help!

 

[Fluff]

Glad to help & thanks for the feedback