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

Occasional Contributor

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

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:

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

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

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

=XMATCH(MAX(range),range))

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

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

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

As variant

=XMATCH(1e20,range,-1)

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

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

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

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

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

@mtarler , good point