Formula not showing result but shows blanks instead

Patcheen

Active Member
Joined
Sep 28, 2015
Messages
391
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.
 
Upvote 0
Yes Oranges thank you it was apples till i adjusted numbers sorry and in relation to the ,0 no it still returns blanks
 
Upvote 0
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
BCD
2OrangesOranges
3
4Oranges
5
6Apples
7
8Oranges
9Apples
10
11Oranges
12Oranges
13Pears
14Pears
15Oranges
16
17Oranges
18Pears
19Pears
20
21Oranges
Sheet1
Cell Formulas
RangeFormula
D2{=INDEX(B2:B21,MODE(IF(B2:B21<>"",MATCH(B2:B21,B2:B21,0))))}
Press CTRL+SHIFT+ENTER to enter array formulas.
 
Last edited:
Upvote 0
Works fine in new sheet but not in the sheet i need it in? confusing or what!
 
Upvote 0
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?
 
Upvote 0
the oranges etc is gotten from other sheet via formula and not manually input. Len?
 
Last edited:
Upvote 0
=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)),"")
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top