Index/mode formula

[TanyaMatt]

I am utilizing the following formula to give me the most common answer from 3 cells. =INDEX(C24:E24,MODE(MATCH(C24:E24,C24:E24,0)))
The issue I am running into is when all 3 cells show a different phrase I am getting this #N/A. How do I build something into this formula to tell excel that if this circumstance happens to choose one of the 3 that I choose to tell it.
Example)
Phrase 1 Phrase 2 Phrase 3
Mouse Cat Dog = #N/A - I would like it in this case to choose Mouse because under the circumstances that created those phrases in those cells mouse had the most opportunity to end up there.

Thanks in advance!

[JohnTopley]

Perhaps you explain " because under the circumstances that created those phrases in those cells mouse had the most opportunity to end up there.

How are we to know that Mouse is "preferred" over Cat or DOG.

Perhaps a sample file would us understand what you require, with logic explained.

[TanyaMatt]

Well the way it got there does not matter for what I am thinking. I essentially am thinking I have chosen what I want it to put instead of #N/A so I do not need excel to choose that. I only need to tell excel that is there is no mode/answer and it is going to produce an answer of #N/A then I want it to choose Mouse, as in mouse the phrase not the cell that contains the phrase Mouse. Does that make a bit more sense then?

[HangMan]

Hi Tanya,

If the outcome should always be the answer from Phrase 1 when the responses to all three phrases are different, simply use an 'IFERROR' to catch the error and decide what the correct response to show should be.

Please Login or Register  to view this content.

If it is not the Phrase 1 answer and an answer you've pre-decided then perhaps

Please Login or Register  to view this content.

It depends on whether the answer you want to show is consistent, if not then the above formula could simply be expanded with some IF Statements, depending on the method you are using to decide the answer you want to show.

[TanyaMatt]

great!! Thank you. =IFERROR(INDEX($C24:$E24,MODE(MATCH($C24:$E24,$C24:$E24,0))),"Mouse") This worked exactly for what I needed. What I wanted was in fact for it to just let me say choose this phrase not this cell this phrase is currently in as it could changed based on other entries. SOLVED!!