Excel - Recursive VLookup

Question

I recently came to know about Ctrl+Shift+Enter array formulas of Excel and currently still learning them. Coming to my problem,

SheetA:
Product        Code
S1             19875
S2             19834
S1             13575
S1             35675
S2             47875   



SheetB:
Code           Indent
19875          40  
19834          15
13575          22
35675          25
47875          20

I need to do Sum of all Indents of a given Product name.

Eg: I need S1's Total Indent,

• Vlookup on SheetA, Get the Code 19875
• Perform a vlookup on SheetB, Get the Indent of 40
• Next Vlookup on Sheet A, Get the code 13575
• Use 13575 to Vlookup on SheetB, Get Indent of 22
• Next Vlookup on Sheet A, Get the code 35675
• Use 35675 to Vlookup on SheetB, get indent of 25
• Sum of 40+22+25, Return 87

I can achieve this through VBA, but I'm wondering if this is possible within excel functions using CSE/Array formulas.

Edit:

I don't have values in Sheet2 in the same order of Sheet1.. They are completely random. My SheetB will be something randomly like following:

SheetB:
Code           Indent
19834          40  
19875          15
47875          22
13575          25
35675          20

Solution

{=SUM(NOT(ISNA(MATCH((($A$2:$A$6="S1")*(B2:B6)),Sheet2!$A$2:$A$6,FALSE)))*(Sheet2!$B$2:$B$6))}

The first argument of the MATCH resolves to

{19875;0;13575;35675;0}

The MATCH resolves to

{1;#N/A;3;4;#N/A}

You'll have to make sure you don't have zeros in SheetB. The NOT ISNA turns those into TRUEs and FALSEs and resolves to

{TRUE;FALSE;TRUE;TRUE;FALSE}

And the final SUM looks like this

=SUM({TRUE;FALSE;TRUE;TRUE;FALSE}*{40;15;22;25;20})

Update

I can't figure out a single-array solution when the lists are in a different order. My attempts with OFFSET and TRANSPOSE either gave the wrong answer or crashed Excel. If you can stand using a helper column, you could put this formula in third column of your first sheet

=VLOOKUP(B2,Sheet2!$A$2:$B$6,2,FALSE)

and then use this array formula to sum them up

{=SUM(($A$2:$A$6=A2)*($C$2:$C$6))}