Forum Discussion
giriokamat
Aug 23, 2022Copper Contributor
Returning part of column header corresponding to matched value in multiple spreadsheets
Looking for output this way: Sheet 1 w 1 e 1 r 2 t 55 y 66 u 2 i 0 q 1 e 1 r 2 Dealing with Multiple sheets with Name and Gender (1,2,3,4,55,66). Code...
- Aug 24, 2022
=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".
OliverScheurich
Aug 24, 2022Gold Contributor
=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".
giriokamat
Aug 25, 2022Copper Contributor
Thank You. I have another follow up question. If I have a few empty cells with no "0's" How can I modify this code to account for this? Please and Thanks. I am using excel 2016.
- OliverScheurichAug 25, 2022Gold Contributor
=IFERROR(INDEX({1,2,3,4,55,66},SUMPRODUCT(($E$3:$E$11=A2)*MMULT(N(($F$3:$K$11<>"")*$F$3:$K$11),TRANSPOSE(COLUMN(F:K))))-5),0)
You can try this formula. Enter the formula with ctrl+shift+enter since you don't work with Office365 or 2021. This example is done in Excel 2013.