Formula to return the contents of a cell next to the largest number.

[mtaylor]

I'm hoping this is a straight forward ask.

I have 4 numbers appearing in the columns C27: C30 (these numbers will be the total points earnt across a competition)

Next to those numbers (in cells: B27:B30) are the respective houses. Please could the "house" next to the greatest number appear in a separate cell (specifically F27)

Thank you

 

[Cubist]

Try:

=INDEX(B27:B30,MATCH(MAX(C27:C30),C27:C30,0))

Other options are available depending on your Excel version.

 

[Scott Huish]

If you have Excel 2021 or later:
=XLOOKUP(MAX(C27:C30),C27:C30,B27:B30,"")

 

[Peter_SSs]

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’)

Is it possible that more than one house earns equal most points? If so (and you have an appropriate version for my suggestion) then ..

24 11 15.xlsm
	B	C	D	E	F
26	House	Points			Highest
27	House 1	15			House 2, House 4
28	House 2	23
29	House 3	4
30	House 4	23
mtaylor

Cell Formulas
Range	Formula
F27	F27	=TEXTJOIN(", ",1,IF(C27:C30=MAX(C27:C30),B27:B30,""))

 

[Scott Huish]

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’)

Is it possible that more than one house earns equal most points? If so (and you have an appropriate version for my suggestion) then ..

24 11 15.xlsm
	B	C	D	E	F
26	House	Points			Highest
27	House 1	15			House 2, House 4
28	House 2	23
29	House 3	4
30	House 4	23
mtaylor

Cell Formulas
Range	Formula
F27	F27	=TEXTJOIN(", ",1,IF(C27:C30=MAX(C27:C30),B27:B30,""))

Same idea but with FILTER
=TEXTJOIN(", ",1,FILTER(B27:B30,C27:C30=MAX(C27:C30)))