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

%3CLINGO-SUB%20id%3D%22lingo-sub-2388599%22%20slang%3D%22en-US%22%3EFind%20max%20value%20in%20a%20data%20set%20but%20assign%20new%20value%20to%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388599%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20using%20windows%2010%20and%20office%20365.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20was%20hoping%20you%20could%20help%20me%20to%20change%20the%20output%20of%20a%20max%20function.%20I'm%20trying%20to%20use%20a%20formula%20that%20would%20assign%20a%20value%20of%201%2F2%2F3%20to%20the%20ouput%20depending%20on%20if%20the%20highest%20value%20is%20in%20column%20A%2FB%2FC.%20For%20example%3A%3C%2FP%3E%3CTABLE%20border%3D%220%22%20width%3D%22191px%22%20cellspacing%3D%220%22%20cellpadding%3D%220%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2247px%22%20height%3D%2219%22%3E967%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E616%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E599%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2247px%22%20height%3D%2219%22%3E440%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E507%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E200%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2247px%22%20height%3D%2219%22%3E271%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E193%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E491%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E3%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%20width%3D%2247px%22%20height%3D%2219%22%3E494%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E534%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E291%3C%2FTD%3E%3CTD%20width%3D%2248px%22%3E2%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhere%20the%20final%20column%20would%20have%20the%20formula%20creating%20the%20results%20of%20the%20max%20from%20the%203%20others.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20hope%20this%20somewhat%20makes%20sense.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20for%20any%20help!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2388599%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388618%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20max%20value%20in%20a%20data%20set%20but%20assign%20new%20value%20to%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388618%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063823%22%20target%3D%22_blank%22%3E%40ExcelLearner2395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DXMATCH(MAX(range)%2Crange))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388619%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20max%20value%20in%20a%20data%20set%20but%20assign%20new%20value%20to%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388619%22%20slang%3D%22en-US%22%3Eyou%20can%20use%3A%3CBR%20%2F%3E%3DMATCH(MAX(A1%3AA3)%2CA1%3AA3%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2388810%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20max%20value%20in%20a%20data%20set%20but%20assign%20new%20value%20to%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2388810%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1063823%22%20target%3D%22_blank%22%3E%40ExcelLearner2395%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXMATCH(1e20%2Crange%2C-1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2389015%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20max%20value%20in%20a%20data%20set%20but%20assign%20new%20value%20to%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389015%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENice%2C%20it's%20so%20easy%20to%20forget%20the%20MAX%2FMIN%20capabilities%20of%20XMATCH%20and%20its%20elder%20sibling%20XLOOKUP.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2389125%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20max%20value%20in%20a%20data%20set%20but%20assign%20new%20value%20to%20result%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2389125%22%20slang%3D%22en-US%22%3Eso%20it%20is%20interesting.%20you%20can%20also%20use%3A%3CBR%20%2F%3E%3DXMATCH(%2Crange%2C-1)%20as%20long%20as%20all%20the%20values%20are%20numeric%20values%20but%20any%20text%26gt%3Bnumber%20and%20blank%20is%20%26gt%3B%20text%3CBR%20%2F%3Ebut%20it%20appears%20you%20can%20use%3A%3CBR%20%2F%3E%3DXMATCH(%22%22%2Crange%2C-1)%20as%20any%20text%20or%20blank%20is%20%26gt%3B%20%22%22%20and%20therefore%20only%20a%20number%20will%20be%20found%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi everyone,

 

I'm using windows 10 and office 365.

 

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:

9676165991
4405072002
2711934913
4945342912

 

Where the final column would have the formula creating the results of the max from the 3 others. 

 

I hope this somewhat makes sense.

 

Thanks for any help! 

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

@ExcelLearner2395 

As variant

=XMATCH(1e20,range,-1)

@Sergei Baklan 

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

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