Max with Index/Match

[joeness419]

Hi All

I am having trouble with the following situation

I have a data set
1 A B C D
2 GDP 100 200 300
3 BBB 400 500 600
4 XYZ 700 800 900

I would like to do an index/match formula that returns the largest value for GDP which would be 300 by searching through B2:D4, not just column D.

Any ideas would be great!

Thanks

Joe

 

[AhoyNC]

Maybe something like this:

Excel Workbook
	A	B	C	D	E	F	G
1	A	B	C	D		Search	GDP
2	GDP	100	200	300			300
3	BBB	400	500	600
4	XYZ	700	800	900
Sheet

 

[joeness419]

Thanks, but what if I want a formula that does all 3 (GDP, BBB, XYZ) at the same time...I would not be able to input $G$1 into the formula....

 

[Special-K99]

What do you expect your output to look like?

 

[MARZIOTULLIO]

[TABLE="class: grid"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]max[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

GDP​

[/TD]
[TD]

100​

[/TD]
[TD]

200​

[/TD]
[TD]

300​

[/TD]
[TD][/TD]
[TD]

300​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

BBB​

[/TD]
[TD]

400​

[/TD]
[TD]

500​

[/TD]
[TD]

600​

[/TD]
[TD][/TD]
[TD]

600​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

XYZ​

[/TD]
[TD]

700​

[/TD]
[TD]

800​

[/TD]
[TD]

900​

[/TD]
[TD][/TD]
[TD]

900​

[/TD]
[/TR]
</tbody>[/TABLE]


f2=MAX(IF($A$2:$A$4=A2,$B$2:$D$4)) control+shift+enter copy down