Indirect Formula

[zinah]

Hi,

I'm struggling to get the right formula to get below dynamic data.

Here's what I have:

Tab that has the data by state and month (Data Tab), and other tab to get the data based on State & Month and whenever I change the month, the data change for each state. In below example data, Jun is drop-down list that has all the month, 6 stands for working month which is Jun in this example, and changes whenever I change the month, i.e. Feb = 2, Jan = 1 and so on.

What I need:

To get the max value of each state by month, using the help of cell that has "6". Anyone can help me with building the right formula using Indirect?

Jun

6​

= to help in formula

Value

Data Tab

State1

Jan

Feb

Mar

Apr

May

Jun

Jul

Aug

Sep

Oct

Nov

Dec

State2

State1

36​

39​

49​

62​

71​

79​

83​

81​

74​

63​

51​

39​

State3

State2

47​

51​

57​

62​

69​

73​

81​

81​

75​

64​

52​

45​

State4

State3

37​

40​

48​

59​

68​

78​

83​

81​

74​

63​

53​

42​

State5

State4

51​

55​

63​

72​

80​

87​

90​

89​

82​

73​

63​

54​

State6

State5

47​

51​

60​

70​

78​

86​

90​

88​

81​

71​

61​

51​

State7

State6

69​

70​

73​

78​

83​

89​

95​

96​

92​

83​

75​

69​

State7

32​

34​

43​

56​

68​

77​

81​

79​

72​

60​

48​

37​

Max Value

69​

70​

73​

78​

83​

89​

95​

96​

92​

83​

75​

69​

 

[Fluff]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	State1	36	39	49	62	71	79	83	81	74	63	51	39
3	State2	47	51	57	62	69	73	81	81	75	64	52	45
4	State3	37	40	48	59	68	78	83	81	74	63	53	42
5	State4	51	55	63	72	80	87	90	89	82	73	63	54
6	State5	47	51	60	70	78	86	90	88	81	71	61	51
7	State6	69	70	73	78	83	89	95	96	92	83	75	69
8	State7	32	34	43	56	68	77	81	79	72	60	48	37
9	Max Value	69	70	73	78	83	89	95	96	92	83	75	69
Data

+Fluff New.xlsm
	A	B	C
1
2	Jun		6
3
4
5
6	State1	79
7	State2	73
8	State3	78
9	State4	87
10	State5	86
11	State6	89
12	State7	77
Cases

Cell Formulas
Range	Formula
B6:B12	B6	=INDEX(Data!$B$2:$M$8,MATCH(A6,Data!$A$2:$A$8,0),$C$2)

 

[zinah]

How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M
1		Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
2	State1	36	39	49	62	71	79	83	81	74	63	51	39
3	State2	47	51	57	62	69	73	81	81	75	64	52	45
4	State3	37	40	48	59	68	78	83	81	74	63	53	42
5	State4	51	55	63	72	80	87	90	89	82	73	63	54
6	State5	47	51	60	70	78	86	90	88	81	71	61	51
7	State6	69	70	73	78	83	89	95	96	92	83	75	69
8	State7	32	34	43	56	68	77	81	79	72	60	48	37
9	Max Value	69	70	73	78	83	89	95	96	92	83	75	69
Data

+Fluff New.xlsm
	A	B	C
1
2	Jun		6
3
4
5
6	State1	79
7	State2	73
8	State3	78
9	State4	87
10	State5	86
11	State6	89
12	State7	77
Cases

Cell Formulas
Range	Formula
B6:B12	B6	=INDEX(Data!$B$2:$M$8,MATCH(A6,Data!$A$2:$A$8,0),$C$2)

Thank you for your input, this is exactly what I wanted

 

[Fluff]

You're welcome & thanks for the feedback.