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

Dick Kusleika Dick Kusleika · Accepted Answer · 2011-03-31T18:41:32

{=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))}

Excel - Recursive VLookup

5 Answers