Forum Discussion
Returning part of column header corresponding to matched value in multiple spreadsheets
=IFERROR(INDEX({1;2;3;4;55;66},MATCH(1,INDEX($F$3:$K$11,MATCH(A2,$E$3:$E$11,0),0),0)),"NO MATCH")This formula returns "no match" for name "w" which is a limitation in my understanding. This is because MATCH returns the first match for every name. The first match for "w" is in cell E3 and there are only 0s in range F3:K3.
=IFERROR(IFERROR(INDEX({1,2,3,4,55,66},SUMPRODUCT(($E$3:$E$11=A2)*MMULT($F$3:$K$11,TRANSPOSE(COLUMN(F:K))))-5),INDEX({1,2,3,4,55,66},SUMPRODUCT(($E$14:$E$18=A2)*MMULT($F$14:$K$18,TRANSPOSE(COLUMN(F:K))))-5)),0)This formula includes another sheet. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021. In the example i've added name "z".
Since you referred the workbook to me, I have, somewhat rudely, made a number of changes!
My version of RollUp uses a slightly less rigorous filter criterion
RollUp
= LET(
stack, VSTACK(tables),
FILTER(stack, TAKE(stack, , 1) <> "")
);Rather than going for FindCol, I used a second XLOOKUP to return the Header directly. I removed an instance of BYROW because the input to the Lambda function had already been reduced to a scalar by the worksheet formula.
FindHdr
= LAMBDA(input,
XLOOKUP(1,
XLOOKUP(input, TAKE(RollUp, , 1), RollUp),
header)
);I converted the BYROW in the worksheet formula to MAP because the names form a single column. By deferring the application of the TEXTAFTER function, I could let the helper function operate directly on the FindHdr lambda function rather than wrapping it within a further LAMBDA to pass the name parameter,
WorksheetFormula
= IFERROR(TEXTAFTER(
MAP(nameList, FindHdr),
" "), 0)There might be advantages in your original use of BYROW in that the inner nested functions could accept array arguments if required. I went for case-specific simplification though.
I welcome different solutions. The OP has 3 solutions to pick from to suit his/her needs and version. Same goes for anyone that may be browsing the discussion.
It is never enough to leave well alone! I have gone for a more verbose formula in which every step is assigned a name in much the same manner as one could us helper ranges in traditional spreadsheets.
= LET( headerValue, VALUE(TEXTAFTER(header," ")), stack, VSTACK(tables), stackedTable, FILTER(stack, TAKE(stack, , 1) <> ""), stackRowHdr, TAKE(stackedTable,,1), stackValues, ReduceTableλ(stackedTable,headerValue), XLOOKUP(nameList, stackRowHdr, stackValues,0) )The first line extracts the final value from each header
The next two lines stack the 3D range and removes empty rows
The table is then reduced to two columns, the first being the row headers and the second contains the values corresponding to the first 1 of each row (using a Lambda function).
These two columns form the lookup array and return array for the final lookup.
ReduceTableλ //"Looks up the first 1 in each row and returns the header" = LAMBDA(table,header, BYROW(table, LAMBDA(row, XLOOKUP(1,row,header,0)) ) );As you see, this is the antithesis of the 'rule of thumb' that says no formula should be longer than your thumb. The idea is more one of 'if the formula is not as long as your arm, you haven't explained the steps of your calculation sufficiently'.
- This one is a bit longer but I like the surgical approach to this task. I like it because it shows how you're stepping through the task step by step. Obtain header values, stack, filter stack, etc.
I can appreciate the 'rule of thumb reference' used correctly. Every time I hear someone say that casually I wonder if he/she knows where it came from!