dynamic list using offset and indirect

Create three dynamic names:
1. AshTable:

Formula:

 =OFFSET(Sheet1!$C$1,1,0,COUNT(Sheet1!$B:$B)+1,5)

2. GATable:

Formula:

=OFFSET(Sheet1!$H$1,1,0,COUNT(Sheet1!$C:$C)+1,5)

3. SolidsTable

Formula:

=OFFSET(Sheet1!$M$1,1,0,COUNT(Sheet1!$C:$C)+1,5)

In your summary/report table use:

=HLOOKUP($S$3,AshTable,ROW()-1,FALSE)
=HLOOKUP($S$3,GATable,ROW()-1,FALSE)
=HLOOKUP($S$3,SolidsTable,ROW()-1,FALSE)

I deleted your named range "test"
I deleted row one
I unmerged cells