Shared Value not being returned using Index/Match and Mode Formula

Hi all,

Following the linked post, I managed to get a really useful formula, which pretty much did everything I needed! https://www.excelforum.com/excel-for...ml#post4649788

However, I have just realised that when the value is shared, it doesn't return a shared value. I assumed it would do because 'Mode' normally just returns the first value when it is shared. Does anyone have any ideas, on how I could amend the following formula, so when the value is shared, it doesn't just return an error, it returns the first value.

Please Login or Register  to view this content.

I've also attached the spreadsheet example.

Any help would be appreciated.

What is you expected result.???

Try

Formula:

Please Login or Register  to view this content.

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Note I still couldn't understand what are your criteria and expected result.

Another way is to coerce a copy of the array. This can be done by adding {0,0} to the conditional MATCH.

Try array entering this modified version of your formula. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Formula:

Please Login or Register  to view this content.

You are welcome. I am glad it helps. Thank you for the feedback.