# Getting array/index formula to ignore blank cells

1. ## Getting array/index formula to ignore blank cells

Hi,

I'm using this formula (entered as an array formula) to return unique values from a range in alphabetical order: =INDEX(\$B\$3:\$B\$11, MATCH(SMALL(IF(COUNTIF(\$D\$2:D2, \$B\$3:\$B\$11)=0, COUNTIF(\$B\$3:\$B\$11, "<"&\$B\$3:\$B\$11), ""), 1), COUNTIF(\$B\$3:\$B\$11, "<"&\$B\$3:\$B\$11), 0)).

The problem is that if there's a blank cell in the range, the formula goes crazy and displays the first (alphabetically speaking) value in all of the rows.

The problem seems to potentially come from this part: COUNTIF(\$D\$2:D2, \$B\$3:\$B\$11). When I use it by itself it returns 0 for all values not found in preceding rows of column D and 1 for all values that are found in precending rows (as it should), but when there's a blank cell in the range it returns values higher than 1 for the instances of the value that would come first in alphabetical order and 0 for all of the other values.

I figure there's probably a way to add the ifblank function somewhere in there that would work, but I don't know where.

Any help would be appreciated.

Simon

2. ## Re: Getting array/index formula to ignore blank cells

For NEXT time, please remember thta it is MUCH easier for us if you provide a sample sheet to to work with.

This is the array formula that you need. You can adapt it to the ranges in your sheet.

=IFERROR(INDEX(\$A\$1:\$A\$20,MATCH(0,COUNTIF(\$A\$1:\$A\$20,"<"&\$A\$1:\$A\$20)+10^10*(\$A\$1:\$A\$20="")-SUM(COUNTIF(\$A\$1:\$A\$20,D\$1:D1)),0)),"")

3. ## Re: Getting array/index formula to ignore blank cells

That works great. Thanks a lot!

4. ## Re: Getting array/index formula to ignore blank cells

You're welcome.

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

5. ## Re: Getting array/index formula to ignore blank cells

Done. Thanks!

6. ## Re: Getting array/index formula to ignore blank cells

Hi,Glenn,
How to solve this with two list data.
(Extract a unique distinct value with 2 list and sort alphabetically)

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

## X vBulletin 4.1.8 Debug Information

• Page Generation 0.06368 seconds
• Memory Usage 9,012KB
• Queries Executed 16 (?)
Template Usage (34):
• (1)footer
• (1)forumrules
• (1)gobutton
• (6)memberaction_dropdown
• (1)navbar
• (4)navbar_noticebit
• (6)option
• (1)postbit_attachment
• (6)postbit_legacy
• (6)postbit_onlinestatus
• (6)postbit_wrapper
• (1)spacer_close
• (1)spacer_open
• (1)tagbit_wrapper

Phrase Groups Available (6):
• global
• inlinemod
• postbit
• posting
• reputationlevel
Included Files (39):
• ./vbseo.php
• ./env.php
• ./vbseo/includes/functions_vbseo.php
• ./vbseo/includes/functions_vbseo_pre.php
• ./vbseo/includes/functions_vbseo_url.php
• ./vbseo/includes/functions_vbseo_createurl.php
• ./vbseo/includes/functions_vbseo_db.php
• ./vbseo/includes/functions_vbseo_vb.php
• ./vbseo/includes/functions_vbseo_seo.php
• ./vbseo/includes/functions_vbseo_misc.php
• ./vbseo/includes/functions_vbseo_crr.php
• ./vbseo/includes/functions_vbseo_cache.php
• ./vbseo/includes/functions_vbseo_hook.php
• ./vbseo/includes/functions_vbseo_startup.php
• ./includes/config.php
• ./global.php
• ./includes/class_bootstrap.php
• ./includes/init.php
• ./includes/class_core.php
• ./includes/functions.php
• ./includes/class_friendly_url.php
• ./includes/class_hook.php
• ./includes/functions_cforum.php
• ./includes/functions_bigthree.php
• ./includes/class_postbit.php
• ./includes/class_bbcode.php
• ./includes/functions_reputation.php
• ./includes/class_bootstrap_framework.php
• ./vb/vb.php
• ./vb/phrase.php
• ./packages/vbattach/attach.php
• ./vb/types.php
• ./vb/cache.php
• ./vb/cache/db.php
• ./vb/cache/observer/db.php
• ./vb/cache/observer.php
• ./includes/functions_notice.php

Hooks Called (50):
• init_startup
• friendlyurl_resolve_class
• database_pre_fetch_array
• database_post_fetch_array
• global_bootstrap_init_start
• global_bootstrap_init_complete
• cache_permissions
• fetch_foruminfo
• global_state_check
• global_bootstrap_complete
• global_start
• style_fetch
• global_setup_complete
• friendlyurl_redirect_canonical
• bbcode_fetch_tags
• bbcode_create
• postbit_factory
• postbit_display_start
• cache_templates
• template_register_var
• parse_templates
• notices_check_start
• notices_noticebit
• process_templates_complete
• reputation_image
• postbit_imicons
• bbcode_parse_start
• bbcode_parse_complete_precache
• bbcode_parse_complete
• postbit_display_complete
• memberaction_dropdown
• postbit_attachment
• tag_fetchbit
• tag_fetchbit_complete