Index Match to find fields based upon Other Worksheets

B24: =SUMIF(B6:B23,"<>#N/A", B6:B23) (solves the N/A issue for items not found)

B26: =COUNTIF(CReport!D:D, $A$2&"")

B28: =IF(ROWS($A$1:$A1)>$B$26, "", INDEX(CReport!$C$1:$C$500, SMALL(IF(CReport!$D$1:$D$500=$A$2&"", ROW($A$1:$A$500)), ROWS($A$1:$A1))))
(this is an array formula, enter it and confirm by pressing CTRL-SHIFT-ENTER to activate the array. Then copy down.)

C28: =IF(ROWS($A$1:$A1)>$B$26, "", INDEX(CReport!$E$1:$E$500, SMALL(IF(CReport!$D$1:$D$500=$A$2&"", ROW($A$1:$A$500)), ROWS($A$1:$A1))))
(this is an array formula, enter it and confirm by pressing CTRL-SHIFT-ENTER to activate the array. Then copy down.)

I applied conditional formatting to A28:A32 and B28:B32 to color the blank cells a different color.