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]

	A​	B​	C​	D​	E​	F​
1​						max
2​	GDP​	100​	200​	300​		300​
3​	BBB​	400​	500​	600​		600​
4​	XYZ​	700​	800​	900​		900​

<tbody>
</tbody>

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