Formula - Index with ignoring blanks

Stengray

New Member
Joined
Aug 13, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Hello there. I have a formula which is returning values from a data set into a list. I am then simplifying that list to ensure there are no repeats. Thus far, I have accomplished this task using the following formula:

Rich (BB code):
=INDEX(ItemSeam,MATCH(0,INDEX(COUNTIF($C$23:C23,ItemSeam),),0))

However, I would also like the index to ignore all blank cells in the original data set. I can't for the life of me figure out how to accomplish this.

My current output:
1597323071750.png



Notes, all data in the data set (ItemSeam) is text (or blank).

Any help would be greatly appreciated!
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,474
Office Version
  1. 365
Platform
  1. Windows
Since you have Excel 365, try this instead. The formula only needs to be entered in the first cell.

20 08 13.xlsm
ABC
22
23Item 1Item 1
24Item 5Item 5
25Item 3Item 3
26Item 22
27Item 22Item 33
28Item 5
29Item 5
30Item 33
31
32Item 1
33
Stengray
Cell Formulas
RangeFormula
C23:C27C23=UNIQUE(FILTER(ItemSeam,ItemSeam<>""))
Dynamic array formulas.
Named Ranges
NameRefers ToCells
ItemSeam=Stengray!$A$23:$A$32C23
 

Stengray

New Member
Joined
Aug 13, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
Thank you Peter_SSs! I do have a couple of issues with this solution though. For some reason, I am getting function not allowed error when running. I am currently on office 356 proplus.
1597326404105.png

The second issue is this excel form will not always be used on a 365 machine, so if I build using this format, my guess is it would not work on machines with the older excel versions?

Again, your help is greatly appreciated!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,474
Office Version
  1. 365
Platform
  1. Windows
The second issue is this excel form will not always be used on a 365 machine, so if I build using this format, my guess is it would not work on machines with the older excel versions?
Correct. That would not work on other versions. Try this modification to your formula.

=IFERROR(INDEX(ItemSeam,MATCH(0,INDEX(COUNTIF($C$23:C23,ItemSeam)+(ItemSeam=""),),0)),"")
 

Stengray

New Member
Joined
Aug 13, 2020
Messages
5
Office Version
  1. 365
Platform
  1. Windows
That works perfect! I need to do some more research so I understand how that works, but thank you very much for your help! Exactly what I needed!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,474
Office Version
  1. 365
Platform
  1. Windows
You're very welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,118,134
Messages
5,570,364
Members
412,321
Latest member
Yusuf_A
Top