Formula

=ArrayFormula({ 'Form Responses 4'!A2:B5, MMULT( VLOOKUP( VLOOKUP( ROW(2:5), {IF(LEN('Form Responses 4'!C2:C5)>0,ROW(2:5),""),'Form Responses 4'!C2:E5},{2,3,4}), {'Data Inputs'!B2:B6,'Data Inputs'!A2:A6}, 2, 0), TRANSPOSE(SIGN(COLUMN('Form Responses 4'!C1:E1)))) })

Input (Form Responses 4)

   |    A    |   B   |   C   |   D   |   E   |   
---+------------------------------------------  
 1 | Key A    Key B   Value   Value   Value
 2 | A        A       Med     Med     Med
 3 | A        A         
 4 | A        A         
 5 | B        A       Low     Med     High

Word/Value table (Data Input)

   |   A   |   B   |
---+----------------
 1 | Value   Word 
 2 |      1  LLow
 3 |      2  Low
 4 |      3  Med
 5 |      4  MedHi
 6 |      5  High  

Result (Calculations)

The column titles are manually entered / copy/pasted. The data below them is calculated; the formula is placed on A2.

   |    A    |   B   |    C    |   
---+---------------------------  
 1 | Key A    Key B   Row Total
 2 | A        A               9
 3 | A        A               9
 4 | A        A               9
 5 | B        A              10

Explanation

In order to keep the complexity at the lowest level that I thinked that is possible for a one formula solution, I avoided to use open references. Once you understand it, you could decide to refine it or to try other thing.

The above formula includes

1. The solution from my answer to An arrayformula to find the previous non-empty cell in another column to fill-up the blanks,
2. A VLOOKUP to replace text by their values, taken from the Data Inputs spreadsheet
3. MMULT to make row sums
4. Arrays to put things together.

If you add more rows to the Form Respondes 4 worksheet, then you should update the following references by replacing the 5 on each appearance of them by the number of the last row to include.

A2:B5
2:5
C2:E5

When stacking arrays vertically, they must have the same number of columns. In case of an error, query outputs a single cell with #REF or #N/A or another message. You put iferror wrapper around it, but that only makes it so the output is one empty cell. Problem is, it is one cell and you need 8 columns to match the other array.

Solution: add a blank row with 8 cells as the second argument of iferror, to be used in case of errors.

=SORT(ArrayFormula({
 IFERROR(QUERY('5 Star Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, F, H, I 
   where A != ' ' AND F IS NOT NULL"),  
 {"","","","","","","",""}); 
 IFERROR(QUERY('5 Star Gear Clubs (Hidden)'!A6:Z, 
  "select A, B, C, D, E, G, H, I 
   where A != ' ' AND F IS NULL"),
 {"","","","","","","",""})
 }),7,FALSE)

Specifically, the error occurs because there is no sheet named '5 Star Gear Clubs (Hidden)'. Suggestion: when a formula throws an error, enter its parts in separate cells (without iferror wrappers) to see what they do.