Excel Formula: Index Array Formula, Skip Cells

Hi,

I have formulated cells between BR18:BR14017 (formulas within the cells are basic IF value then show, otherwise blank). However within that range there are several blank cells between cells with actual data.


BR18 = Formula returned: Math
BR19 = Formula returned: Blank
BR20 = Formula returned: Blank
BR21 = Formula returned: Blank
BR22 = Formula returned: Blank
BR23 = Formula returned: English


My goal is to have a formula between EH3:EH14002 that would only show the non-blank returned cells - in the above example:

Math
English

why not use the built in filter and filter on non blank (click of blank).

Try this array formula** entered in EH3:

=IFERROR(INDEX(BR:BR,SMALL(IF(BR$18:BR$14017<>"",ROW(BR$18:BR$14017)),ROWS(EH$3:EH3))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

I almost forgot...

Copy down until you get blanks!

You're welcome. Thanks for the feedback!