Return 2nd/3rd/4th...non-blank cells in a column

[lambed3]

I am trying to have Excel do a lookup to a data table that is arranged from 1-8 based on priority but at times 1 of the 8 options (random) are not a viable option so the data table uses a formula to return a blank value for that option. The table I am trying to fill in is to take the first 5 non-blank values and skip over the blanks (go to the next highest priority). The formula below works for the most part except for the fact that the cells that are blank still have a formula in them. When I delete the formula for the blank cells, the formula works perfectly but with the formula included and a blank result the top 5 includes a blank result as well. Here is my formula and an example of how this should work:

=INDEX($Q$3:$X$3,,SMALL(IF(ISBLANK($Q$3:$X$3),"",COLUMN($Q$3:$X$3)),1)-COLUMN($Q$3:$X$3)+1)

I am using this as an array with Ctrl+Alt+Enter

Data Table with Formulas

Description P1 P2 P3 P4 P5 P6 P7 P8
Description1 A T (blank) R E (blank) 2 1
Description2 D Q X (blank) H A (blank) G
Description3 (blank) Q Z (blank) P O L X
Description4 A S V L F Z Y (blank)

Remember, all of the blanks above still have a formula (if statement with a "" result).

Desired result
Description P1 P2 P3 P4 P5
Description1 A T R E 2
Description2 D Q X H A
Description3 Q Z P O L
Description4 A S V L F

 

[lambed3]

One other thing to clarify in the formula:

=INDEX($Q$3:$X$3,,SMALL(IF(ISBLANK($Q$3:$X$3),"",COLUMN($Q$3:$X$3)),1)-COLUMN($Q$3:$X$3)+1)

I am changing the "1" in the middle of the formula (not the +1 on the end) to 2,3,4,5 to return the 1st through fifth non-blank values.

 

[Marcelo Branco]

Maybe this array-formula

=IF(COLUMNS($Q3:Q3)<=5,INDEX($Q3:$X3,SMALL(IF($Q3:$X3<>"",COLUMN($Q3:$X3)-COLUMN($Q3)+1),COLUMNS($Q3:Q3))),"")

Ctrl+Shift+Enter

copy across and down

M.

 

[lambed3]

This is great. Thanks so much for the quick reply.

 

[Marcelo Branco]

This is great. Thanks so much for the quick reply.

You are welcome and tks for the feedback

M.