Which group is my valu in?

[osianap]

Cells A1:A4 (group 1) contain the values 1, 2, 3, 4
Cells B1:B4 (group 2) contain the values 5, 6, 7, 8
Cells C1:C4 (group 3) contain the values 9, 10, 11, 12

Cell A6 contains my variable value - use for example 7.
Cell B6 should contain the value "2", because 7 is in group 2.

My question: What is the best formula in cell B6? No VB scripting please!


P.S.: It is safe to assume that all groups are mutually exclusive (i.e. no single digit exists in more than one group).

 

[Eric W]

Maybe:

=MIN(IF(A1:C4=A6,COLUMN(A1:C4)))
Confirmed with Control+Shift+Enter

 

[jtakw]

Hi,

Another way:

Excel 2010
	A	B	C	D
1	Group	1	2	3
2		1	5	9
3		2	6	10
4		3	7	11
5		4	8	12
6
7		7	2
Sheet1

Cell Formulas
Range	Formula
C7	=SUMPRODUCT((B2:D5=B7)*(B1:D1))

 

[63falcondude]

	A	B	C
1	Group 1	Group 2	Group 3
2	1	5	9
3	2	6	10
4	3	7	11
5	4	8	12
6
7	Variable Value	Corresponding Group
8	7

<tbody>
</tbody>

In cell B2 type in =INDEX(A1:C1,SUMPRODUCT(COLUMN(A2:C5)*(A2:C5=A8)))

It will return Group 2

 

[West Man]

Another way =INT((A6-1)/4)+1