Help with a simple INDEX MATCH

[Mark Carver]

I have a simple problem but I can't seem to figure it out. It should be easy. Can anyone help? I have a row containing years and another containing value. I want to find the MAX value and return the year. See the attached spreadsheet. THANKS!

Mark

 

[Chief Ace]

Hope this helps. I kind of rearrange the format. Please see sample file.

 

[bosco_yip]

Try,

In "year" D7, enter :

=INDEX(C3:G3,MATCH(C7,C5:G5,0))

or,

=SUMIF(C5:G5,C7,C3:G3)

Regards
Bosco

 

[Peter Bartholomew]

To add a further option to the collection:
= LOOKUP( 1, 1/(MAX(Cost)=Cost), Year)

 

[Peter Bartholomew]

I might as well include the (Excel 365) file to demonstrate the formulae.

To me, it also serves as a demonstration that direct referencing is simply an unnecessary complexity introduced within the original spreadsheet concept. It is always far more meaningful to identify objects by name than it is to resort to location by coordinates.

p.s. I realise (almost all) others disagree.

 

[AliGW]

Nothing wrong with being an evangelist.

 

[Peter Bartholomew]

Thanks @Ali. Don't forget to warn me before I get really annoying though ,

 

[Mark Carver]

Thank you, all of you!