Get Value From Report

[LearnMeExcel]

Hi All
i have this data

2010​

2011​

2012​

2013​

2014​

2015​

2016​

2017​

2018​

2019​

2020​

2021​

Jan

45,149.60​

76,249.86​

72,412.60​

23,409.26​

83,908.23​

82,724.74​

49,913.37​

70,728.86​

4,416.54​

61,071.23​

22,704.21​

76,804.66​

Feb

30,555.36​

73,104.39​

57,297.66​

89,688.13​

50,165.71​

60,077.21​

13,531.12​

49,570.29​

59,496.03​

8,921.62​

92,197.73​

67,460.36​

Mar

21,608.99​

36,339.27​

21,235.38​

52,418.65​

61,033.78​

50,561.46​

78,225.45​

31,191.93​

91,151.37​

91,178.09​

74,800.71​

80,748.89​

Apr

81,801.81​

35,501.58​

29,073.81​

45,339.68​

34,752.21​

23,995.73​

12,659.20​

4,947.22​

67,160.00​

55,131.82​

15,710.53​

15,579.69​

May

12,199.39​

78,509.38​

31,039.96​

77,098.36​

21,619.35​

32,470.22​

49,348.30​

65,940.48​

5,120.67​

36,043.77​

5,838.33​

92,043.66​

Jun

12,661.03​

45,782.26​

21,403.17​

51,664.34​

54,511.89​

94,633.87​

12,896.98​

93,584.13​

12,803.14​

63,584.26​

80,878.97​

35,968.93​

Jul

65,555.15​

97,417.43​

54,882.24​

44,430.13​

36,346.93​

66,670.34​

78,241.86​

65,785.69​

28,351.80​

29,499.59​

91,120.24​

79,486.58​

Aug

90,536.96​

98,957.46​

44,090.10​

66,568.31​

2,656.73​

87,136.34​

49,877.47​

74,783.41​

57,351.77​

38,779.90​

57,660.81​

66,435.26​

Sep

12,839.83​

91,225.23​

48,399.98​

54,662.50​

6,512.15​

20,346.65​

75,301.21​

67,279.38​

79,972.44​

81,113.93​

12,086.08​

35,788.94​

Oct

30,006.08​

67,379.42​

64,291.48​

24,467.70​

98,809.45​

68,517.80​

67,606.99​

69,920.49​

80,764.32​

88,669.03​

20,104.29​

23,344.84​

Nov

94,846.19​

55,164.55​

55,159.90​

73,427.91​

66,821.75​

44,138.21​

14,348.50​

71,831.44​

86,411.27​

32,776.46​

80,330.05​

82,913.78​

Dec

61,213.79​

82,239.09​

54,829.66​

19,752.35​

86,381.06​

40,036.52​

11,423.66​

3,042.42​

37,248.81​

47,619.12​

88,888.41​

85,887.80​

in range A2:M14
and i get the highest Sales From This Table by Function Large

highest sales​	Year​	Month​
98,957.46​
98,809.45​
97,417.43​
94,846.19​
94,633.87​
93,584.13​

my Question
how i can get the name of month and year From Each one of this Values
the result for first line
98957.46 Yeas is 2011 Month Aug

i need formula to do that without Changing Data layout

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[LearnMeExcel]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thank you
i updated it

 

[Fluff]

Thanks for that.
How about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021		Large	Month	Year
2	Jan	45,149.60	76,249.86	72,412.60	23,409.26	83,908.23	82,724.74	49,913.37	70,728.86	4,416.54	61,071.23	22,704.21	76,804.66		98957.46	Aug	2011
3	Feb	30,555.36	73,104.39	57,297.66	89,688.13	50,165.71	60,077.21	13,531.12	49,570.29	59,496.03	8,921.62	92,197.73	67,460.36		98809.45	Oct	2014
4	Mar	21,608.99	36,339.27	21,235.38	52,418.65	61,033.78	50,561.46	78,225.45	31,191.93	91,151.37	91,178.09	74,800.71	80,748.89		97417.43	Jul	2011
5	Apr	81,801.81	35,501.58	29,073.81	45,339.68	34,752.21	23,995.73	12,659.20	4,947.22	67,160.00	55,131.82	15,710.53	15,579.69		94846.19	Nov	2010
6	May	12,199.39	78,509.38	31,039.96	77,098.36	21,619.35	32,470.22	49,348.30	65,940.48	5,120.67	36,043.77	5,838.33	92,043.66		94633.87	Jun	2015
7	Jun	12,661.03	45,782.26	21,403.17	51,664.34	54,511.89	94,633.87	12,896.98	93,584.13	12,803.14	63,584.26	80,878.97	35,968.93		93584.13	Jun	2017
8	Jul	65,555.15	97,417.43	54,882.24	44,430.13	36,346.93	66,670.34	78,241.86	65,785.69	28,351.80	29,499.59	91,120.24	79,486.58
9	Aug	90,536.96	98,957.46	44,090.10	66,568.31	2,656.73	87,136.34	49,877.47	74,783.41	57,351.77	38,779.90	57,660.81	66,435.26
10	Sep	12,839.83	91,225.23	48,399.98	54,662.50	6,512.15	20,346.65	75,301.21	67,279.38	79,972.44	81,113.93	12,086.08	35,788.94
11	Oct	30,006.08	67,379.42	64,291.48	24,467.70	98,809.45	68,517.80	67,606.99	69,920.49	80,764.32	88,669.03	20,104.29	23,344.84
12	Nov	94,846.19	55,164.55	55,159.90	73,427.91	66,821.75	44,138.21	14,348.50	71,831.44	86,411.27	32,776.46	80,330.05	82,913.78
13	Dec	61,213.79	82,239.09	54,829.66	19,752.35	86,381.06	40,036.52	11,423.66	3,042.42	37,248.81	47,619.12	88,888.41	85,887.80
14
15
Main

Cell Formulas
Range	Formula
O2:O7	O2	=LARGE(B2:M13,SEQUENCE(6))
P2:P7	P2	=INDEX($A$2:$A$13,AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($B$2:$M$13=O2),1))
Q2:Q7	Q2	=TEXTJOIN("",,IF(($A$2:$A$13=P2)*($B$2:$M$13=O2),$B$1:$M$1,""))
Dynamic array formulas.

 

[LearnMeExcel]

wow
thank you Fluff, amazing!
but this formula only for new version of excel, not for old one if it is not Contains TextJoin Function, Right

 

[Fluff]

You're welcome & thanks for the feedback.
It will work with 2019 or newer.

 

[LearnMeExcel]

You're welcome & thanks for the feedback.
It will work with 2019 or newer.

yes it is clear
Thank you again

 

[Fluff]

My pleasure.

 

[LearnMeExcel]

if we have Duplicate,

2010​

2011​

2012​

2013​

2014​

2015​

2016​

2017​

2018​

2019​

2020​

2021​

Large

Month

Year

Correct Result

Jan

99,999.00​

99,999.00​

72,412.60​

23,409.26​

83,908.23​

82,724.74​

49,913.37​

70,728.86​

4,416.54​

61,071.23​

22,704.21​

76,804.66​

99,999.00​

Jan

20102011

Jan

2010​

Feb

99,999.00​

73,104.39​

57,297.66​

89,688.13​

50,165.71​

60,077.21​

13,531.12​

49,570.29​

59,496.03​

8,921.62​

92,197.73​

67,460.36​

99,999.00​

Jan

20102011

Feb

2010​

Mar

21,608.99​

36,339.27​

21,235.38​

52,418.65​

61,033.78​

50,561.46​

78,225.45​

31,191.93​

91,151.37​

91,178.09​

74,800.71​

80,748.89​

99,999.00​

Jan

20102011

Jan

2011​

Apr

81,801.81​

35,501.58​

29,073.81​

45,339.68​

34,752.21​

23,995.73​

12,659.20​

4,947.22​

67,160.00​

55,131.82​

15,710.53​

15,579.69​

98,957.46​

Aug

2011

May

12,199.39​

78,509.38​

31,039.96​

77,098.36​

21,619.35​

32,470.22​

49,348.30​

65,940.48​

5,120.67​

36,043.77​

5,838.33​

92,043.66​

98,809.45​

Oct

2014

Jun

12,661.03​

45,782.26​

21,403.17​

51,664.34​

54,511.89​

94,633.87​

12,896.98​

93,584.13​

12,803.14​

63,584.26​

80,878.97​

35,968.93​

97,417.43​

Jul

2011

Jul

65,555.15​

97,417.43​

54,882.24​

44,430.13​

36,346.93​

66,670.34​

78,241.86​

65,785.69​

28,351.80​

29,499.59​

91,120.24​

79,486.58​

Aug

90,536.96​

98,957.46​

44,090.10​

66,568.31​

2,656.73​

87,136.34​

49,877.47​

74,783.41​

57,351.77​

38,779.90​

57,660.81​

66,435.26​

Sep

12,839.83​

91,225.23​

48,399.98​

54,662.50​

6,512.15​

20,346.65​

75,301.21​

67,279.38​

79,972.44​

81,113.93​

12,086.08​

35,788.94​

Oct

30,006.08​

67,379.42​

64,291.48​

24,467.70​

98,809.45​

68,517.80​

67,606.99​

69,920.49​

80,764.32​

88,669.03​

20,104.29​

23,344.84​

Nov

94,846.19​

55,164.55​

55,159.90​

73,427.91​

66,821.75​

44,138.21​

14,348.50​

71,831.44​

86,411.27​

32,776.46​

80,330.05​

82,913.78​

Dec

61,213.79​

82,239.09​

54,829.66​

19,752.35​

86,381.06​

40,036.52​

11,423.66​

3,042.42​

37,248.81​

47,619.12​

88,888.41​

85,887.80​

the result by formula not Correct, can we solve it.

 

[Fluff]

Ok, how about

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		2010	2011	2012	2013	2014	2015	2016	2017	2018	2019	2020	2021		Large	Month	Year
2	Jan	99,999.00	99,999.00	72,412.60	23,409.26	83,908.23	82,724.74	49,913.37	70,728.86	4,416.54	61,071.23	22,704.21	76,804.66		99999.00	Jan	2010
3	Feb	99,999.00	73,104.39	57,297.66	89,688.13	50,165.71	60,077.21	13,531.12	49,570.29	59,496.03	8,921.62	92,197.73	67,460.36		99999.00	Jan	2011
4	Mar	21,608.99	36,339.27	21,235.38	52,418.65	61,033.78	50,561.46	78,225.45	31,191.93	91,151.37	91,178.09	74,800.71	80,748.89		99999.00	Feb	2010
5	Apr	81,801.81	35,501.58	29,073.81	45,339.68	34,752.21	23,995.73	12,659.20	4,947.22	67,160.00	55,131.82	15,710.53	15,579.69		98957.46	Aug	2011
6	May	12,199.39	78,509.38	31,039.96	77,098.36	21,619.35	32,470.22	49,348.30	65,940.48	5,120.67	36,043.77	5,838.33	92,043.66		98809.45	Oct	2014
7	Jun	12,661.03	45,782.26	21,403.17	51,664.34	54,511.89	94,633.87	12,896.98	93,584.13	12,803.14	63,584.26	80,878.97	35,968.93		97417.43	Jul	2011
8	Jul	65,555.15	97,417.43	54,882.24	44,430.13	36,346.93	66,670.34	78,241.86	65,785.69	28,351.80	29,499.59	91,120.24	79,486.58
9	Aug	90,536.96	98,957.46	44,090.10	66,568.31	2,656.73	87,136.34	49,877.47	74,783.41	57,351.77	38,779.90	57,660.81	66,435.26
10	Sep	12,839.83	91,225.23	48,399.98	54,662.50	6,512.15	20,346.65	75,301.21	67,279.38	79,972.44	81,113.93	12,086.08	35,788.94
11	Oct	30,006.08	67,379.42	64,291.48	24,467.70	98,809.45	68,517.80	67,606.99	69,920.49	80,764.32	88,669.03	20,104.29	23,344.84
12	Nov	94,846.19	55,164.55	55,159.90	73,427.91	66,821.75	44,138.21	14,348.50	71,831.44	86,411.27	32,776.46	80,330.05	82,913.78
13	Dec	61,213.79	82,239.09	54,829.66	19,752.35	86,381.06	40,036.52	11,423.66	3,042.42	37,248.81	47,619.12	88,888.41	85,887.80
14
Main

Cell Formulas
Range	Formula
O2:O7	O2	=LARGE(B2:M13,SEQUENCE(6))
P2:P7	P2	=INDEX($A$2:$A$13,AGGREGATE(15,6,(ROW($A$2:$A$13)-ROW($A$2)+1)/($B$2:$M$13=O2),COUNTIFS(O$2:O2,O2)))
Q2:Q7	Q2	=INDEX($B$1:$M$1,AGGREGATE(15,6,(COLUMN($B$1:$M$1)-COLUMN($B$1)+1)/($A$2:$A$13=P2)/($B$2:$M$13=O2),COUNTIFS(P$2:P2,P2)))
Dynamic array formulas.