I have data in column C and E. The values in both these columns is random. I'm looking for the highest value to be returned in column I. So I have =MAX in column I. I would like column J to return which winning column this was taken from. So my labels for column C & E are in listed in C3 & E3. What formula can I use to return these?

My Random data is in Col C7 Col E7

Col Labels are Col C3 Col E3

My winning selection is in I7

Would like The data in Col J7 to be returned from either C3 or E3 based on the value in I7.

Can you help?

  • How is your current MAX function written? Are all your values in C & E put together distinct?Can there be repeating MAX value in Col C as well as E? May be a sample screenshot could help solve your query. – pat2015 Jan 30 '17 at 11:40
  • prnt.sc/e26ke8 – Johnnyenv Jan 30 '17 at 11:44
  • What you wish to return in Col J? Should it simply be Column Name say Col C or Col E based on where the MAX value for that row is? And what if values in Col C & E are equal, there's a tie? – pat2015 Jan 30 '17 at 11:53
  • The returned value in J7 should be either "Desktop" or "Mobile" based on where the value is picked up from. – Johnnyenv Jan 30 '17 at 11:55

One solution is to use MATCH function to check where match exists and based on the IF criteria return either "Desktop" or "Mobile" in Column I. This solution shall not factor in Tie case. In I7 Put

=MAX(C7,E7)

In J7 put

=IF(ISERROR(MATCH(I7,C7,0)),"Mobile","Desktop")

and drag it down.

enter image description here

And just in case you wish to factor in Tie and return it as "Tie" in Col J, put the following formula in I7 and drag it down

=IF(AND(EXACT(I7,C7),EXACT(I7,E7)),"Tie",IF(EXACT(I7,C7),"Desktop","Mobile"))

enter image description here

In I7, your formula should be:

=MAX(C7,E7)

And in J7, your formula should be:

=IF(C7>E7,$B$4,IF(C7<E7,$E$4,"Equal"))

This will output "Test A" if C is greater than E, "Test B" if E is greater than C, and "Equal" otherwise.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.