How to sum a row in Excel with lookup values from a column based lookup?

Question

I have rows of values in columns AC to AU (but also some blanks)

I want to convert these values in a formula via a lookup and sum all the lookup values in a row.

For 1 cell I can use VLOOKUP(AC2,'Lookup_Table'!A2:B41,2) successfully

How do I sum the whole row of AC2 to AU2 with this kind of lookup?

Note the lookup is column oriented in a vertical lookup, the 'Lookup Table' is actually a worksheet.

This has stumped me for hours now!

         | AC | AD | AE ..Ax... AU
   Row 1 | 12 | 23 | 43 ..00... 67

    'Lookup table' worksheet
        A  |  B
        12 |  4
        23 |  2
        43 |  3
        67 |  5

The result should be 14 for the sample Row 1 data above

Solution

It is much easier to use an array formula (ctrl+shift+enter instead of just enter). Generally I don't like array formulas (because they are easy for the uneducated to break), but I think it will work the best in your situation apart from reformatting your sheet or using helper columns.

{=SUM(IF(AC2:AU2=Lookup_Table!A2:A41,Lookup_Table!B2:B41))}

which should have {} around the whole thing if you entered it correctly.