Lookup functions to skip over blanks

Hi, this is my first time posting. I apologize if I mess up forum protocols. I think my question is a combination of dashboards and Array Functions.

I have 4 Columns (A - "Earmark", B - "Category", C - "Sub Category", D - "Cost"). I am trying to pull specific values based on a text value that I'm putting in Column (A). The text I put in Column (A) is essentially my way of earmarking the rows I want to group elseward in the sheet (like a dashboard).

MY GOALS:
(1) to create a formula (or formulas) that I can copy down
(2) IF there is a "C" in Column (A), then pull the value from same row in Column (D). If the value in (D) is blank then pull a blank - not a 0.
(3) IF there is a "s" in Column (A), then pull the value from same row in Column (C) and also Column (D)
(4) When the formula is copied down, my dashboard list will skip over any blanks from Column (A) and list the next non-blank row that has a "C" or "s" in Column (A)

So far my mini-dashboard I'm using 2 formulas in 2 different columns...
Column #1 "Item": =IF(A3="","",IFERROR(VLOOKUP("C",A3:D3,2,0),IFERROR(VLOOKUP("s",A3:D3,3,0),)))
Column #2 "Cost": =IF(A3="","",IFERROR(VLOOKUP("C",A3:D3,4,0),IFERROR(VLOOKUP("s",A3:D3,4,0),)))

MY QUESTION:
Is there a way to omit the blanks and have the new list build itself omitting blanks?

Thanks in advance for any ideas or help

Welcome to the forum.

I took a different approach to Glenn, and used this formula in cell E3:

=IF(OR(A3="C",A3="s"),MAX(E$2:E2)+1,"")

and then copied this down to beyond the bottom of your data, to allow for more data being added. This formula identifies the records that you want to include, and allocates a unique sequential number to each. I assumed you wanted your output table in columns F and G, so I moved your (manual) table across a few columns so that you could compare results directly, and then I put this formula in cell F7:

=IF(COUNTIF($E:$E,ROWS($1:1))=0,"",IF(AND(INDEX($A:$A,MATCH(ROWS($1:1),$E:$E,0))="C",COLUMNS($F:F)>1),"",INDEX($B:$D,MATCH(ROWS($1:1),$E:$E,0),COLUMNS($F:F)+IF(INDEX($A:$A,MATCH(ROWS($1:1),$E:$E,0))="C",0,1))))

The formula can be copied across into G7, and then F7:G7 can be copied down as far as you need it. The attached file shows the result.

Hope this helps.

Pete

Wow, thank you all so much. This is really helpful!! I really appreciate it.

All set, I did both. Thank you all again. This is a great forum and very professional. I'm glad I joined.