Find max value in a data set but assign new value to result

Question

Hi everyone,&nbsp;I'm using windows 10 and office 365.&nbsp;I was hoping you could help me to change the output of a max function. I'm trying to use a formula that would assign a value of 1/2/3 to the ouput depending on if the highest value is in column A/B/C. For example:9676165991440507200227119349134945342912&nbsp;Where the final column would have the formula creating the results of the max from the 3 others.&nbsp;&nbsp;I hope this somewhat makes sense.&nbsp;Thanks for any help!&nbsp;

sergeibaklan · Answer

ExcelLearner2395&nbsp;
As variant
=XMATCH(1e20,range,-1)

detlef_lewin · Answer

ExcelLearner2395&nbsp;=XMATCH(MAX(range),range))

mtarler · Answer

you can use:=MATCH(MAX(A1:A3),A1:A3,0)

peterbartholomew1 · Answer

SergeiBaklan&nbsp;Nice, it's so easy to forget the MAX/MIN capabilities of XMATCH and its elder sibling XLOOKUP.&nbsp;

mtarler · Answer

so it is interesting. you can also use:=XMATCH(,range,-1) as long as all the values are numeric values but any text&gt;number and blank is &gt; textbut it appears you can use:=XMATCH("",range,-1) as any text or blank is &gt; "" and therefore only a number will be found

Forum Discussion

Find max value in a data set but assign new value to result

6 Replies

Resources

967	616	599	1
440	507	200	2
271	193	491	3
494	534	291	2