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

 

[FormR]

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

Hi, wouldn't you expect "Oranges" to be returned?

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

With your formula and example data I get "Oranges" - but try removing the red highlighted section and see if it helps.

 

[Patcheen]

Yes Oranges thank you it was apples till i adjusted numbers sorry and in relation to the ,0 no it still returns blanks

 

[FormR]

It's difficult to see how that's possible with that exact set-up. If you reproduce this in a new workbook do you still get blanks?

Excel 2013/2016
	B	C	D
2	Oranges		Oranges
3
4	Oranges
5
6	Apples
7
8	Oranges
9	Apples
10
11	Oranges
12	Oranges
13	Pears
14	Pears
15	Oranges
16
17	Oranges
18	Pears
19	Pears
20
21	Oranges
Sheet1

Cell Formulas
Range	Formula
D2	{=INDEX(B2:B21,MODE(IF(B2:B21<>"",MATCH(B2:B21,B2:B21,0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Patcheen]

Works fine in new sheet but not in the sheet i need it in? confusing or what!

 

[FormR]

Works fine in new sheet but not in the sheet i need it in? confusing or what!

What is different about the sheet you need it in?

- Is it exactly the same formula? - if not post what you are actually using.
- Are there more blanks than anything else in the range? - if so, post an accurate example of what is in the range.
- Are the blank cells really blank? - what does this return =LEN(B2) when pointed at one of the blank cells?

 

[Patcheen]

the oranges etc is gotten from other sheet via formula and not manually input. Len?

 

[FormR]

the oranges etc is gotten from other sheet via formula and not manually input. Len?

What is that formula?

 

[Patcheen]

=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)),"")

 

[FormR]

And the other questions?

Note, you can see your workbook, we can't - you will need to investigate and dig around to find out what is different in your actual workbook.

Worst comes to worst, remove any sensitive data and upload your workbook to a file sharing site and share the link here so we can take a look.