So I am trying to find a formula that I will put in cell A6, that will search the cells A3:G3 for a max value and will return the value of the cell above the max value. In the case of the example photo i want the formula to be able to return the value of E2 sice E3 is the highest value.

See photo here!

Also, in case of same values, I would like it to return all days separated with a comma or something.

I hope my description wasn't very confusing. Thank you for your time and help.

CodePudding user response：

Why don't try the simple max to achieve the result.

=MAX(A3:G3)

Okay to get the right result, you have to do the following.

=INDEX(A2:G2,MATCH(MAX(A3:G3),A3:G3,0))

Since your data is more likely in Row, so VLookup is not useful and Hlookup won't get max. Index Match formula is used to get Max.

CodePudding user response：

If no extra cell for intermediate calculation, textjoin function will be needed:

=textjoin(",",TRUE,if(A3:G3=max(A3:G3),A2:G2,""))

for the worst, just change max to min and this is array formula, ctrl shift enter is required