If you list your Lookup_table with column a sorted alphabetically as per your sample, then....

Assuming Lookup_table is in A1:B6, incl. headers and Data_table is in D1:E9, then try:

=SUMPRODUCT(--(LOOKUP($D$2:$D$9,$A$2:$B$6)="High"),$E$2:$E$9)

you can replace "High" with a cell reference.