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".
Here's a 365 solution with Lambda.
Roll up the data with VSTACK, add a totals column to determine which rows = 0, and then filter out rows with all 0s.
=LET(stack,VSTACK(Sheet2:Sheet3!$A$1:$G$11),totals,BYROW(stack,LAMBDA(row,SUM(row))),FILTER(stack,totals<>0))Lambda to determine the column number for a given input containing a 1.
=LAMBDA(input,BYROW(input,LAMBDA(row,XMATCH(1,XLOOKUP(row,TAKE(RollUp,,1),RollUp)))))Formula to deliver the results:
=IFERROR(BYROW(A2:A11,LAMBDA(col,TEXTAFTER(CHOOSECOLS(header,FindCol(col))," "))),0)
PeterBartholomew1 may be interested in giriokamat 's request. Including him here.
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.
- Thank you for sharing. That's some excellent efficiency. When there's no need to obtain the column number to pick from the header, it makes things a bit simpler. It seems there is some pattern building with these exercises and methods for obtaining a solution. Any time multiple sheets are involved it becomes a question of using VSTACK or TOCOL, for example. If VSTACK is used, then inevitably FILTER will follow in some capacity. If TOCOL is used, there will be some wrapping more than likely. It's very stimulating and fun to have plenty of options!
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'.
