Formula not showing result but shows blanks instead

[Patcheen]

Im using this formula to eliminate any blanks and return the most item value instead...

{=INDEX(B2:B21,MODE(IF(B2:B21<>"",MATCH(B2:B21,B2:B21,0),0)))} (Array Formula)

but it still returns blanks

example in b2:b21 is - oranges x8 - apples x2 - pears x4 and blanks (no value) x6 (it should show "apples"

the X represents the amount of times the word appears in the row from b2:b21 etc etc


thank you all in advance for your input and help

 

[Patcheen]

yes its returning zero's

 

[FormR]

yes its returning zero's

Change it to this:

=IFERROR(INDEX('Sheet 1'!$M$4:$M$383,MATCH(B$1&$A18,'Sheet 1'!$F$4:$F$383&'Sheet 1'!$I$4:$I$383,0)),0)

And change your mode formula to:

=INDEX(B2:B21,MODE(IF(B2:B21<>0,MATCH(B2:B21,B2:B21,0))))

Both of these are array formula.

EDIT NOTE: Removed that else zero from the IF() function

 

[Patcheen]

thats it ta finally says you thank you so very much for your help