Formula - Index with ignoring blanks

[Stengray]

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:

=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:

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

Any help would be greatly appreciated!

 

[Peter_SSs]

Since you have Excel 365, try this instead. The formula only needs to be entered in the first cell.

20 08 13.xlsm
	A	B	C
22
23	Item 1		Item 1
24	Item 5		Item 5
25	Item 3		Item 3
26			Item 22
27	Item 22		Item 33
28	Item 5
29	Item 5
30	Item 33
31
32	Item 1
33
Stengray

Cell Formulas
Range	Formula
C23:C27	C23	=UNIQUE(FILTER(ItemSeam,ItemSeam<>""))
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
ItemSeam	=Stengray!$A$23:$A$32	C23

 

[Stengray]

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.

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]

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]

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]

You're very welcome. Thanks for the follow-up.