Multilevel reverse lookup using array returned from INDEX-MATCH as keys

0
votes

In my use-case, when I use INDEX-MATCH I get an array. The array corresponds to some keys in another sheet where the column A are the keys and the next column B are the values. How do I use the returned array of keys and look them up in the other sheet, get the corresponding value, and sum the values?

To make this more concrete:

I have three sheets, weights, bags, and totals.

weights has items in column A and their corresponding weight in column B.

enter image description here

bags has each row with the column A as the bag name and all other columns as items in each bag. the items are from weghts!A1:A

enter image description here

totals has column A as the name of the bag from bags sheet. The second column should be the total weight of all items inside that bag.

enter image description here

The items themselves can be found in the bags sheet using the formula

INDEX(bags!B:Z, MATCH(A1, bags!A1:A))

How do I use this array of results to find their weights in weights and sum them up? I would like to not need to modify the structure of the sheets.

1
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
Excel, or Google sheets? Either way ,it's useful to show exactly what you're doing. - Tim Williams
Google sheets... - Sanfer
@TimWilliams take a look at the edit, I've elaborated on the question - Sanfer

1 Answers

0
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(K1:K, IF(D1:D<>"", {D1:D, 
 MMULT(IFERROR(REGEXREPLACE(E1:I, 
 TEXTJOIN("|", 1, A1:A), VLOOKUP(REGEXEXTRACT(E1:I, 
 TEXTJOIN("|", 1, A1:A)), A1:B, 2*SIGN(ROW(A1:A)), 0)&""), E1:I)*1, 
 TRANSPOSE(COLUMN(E1:I))^0)}, ), 2, 0)))

0