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".
I was able to get this output with
=TEXTJOIN(";",TRUE,IF(INDEX(Sheet2!$B$2:$G$10,MATCH(A2,Sheet2!$A$2:$A$10,0),0)=1,{1,2,3,4,55,66},"")). How can i modify this to get the first row corresponding to "w" working. I
| w | |
| e | 1 |
| r | 2 |
| t | 55 |
| y | 66 |
| u | 2 |
| i | #N/A |
| q | 1 |
| e | 1 |
| r | 2 |
=IFERROR(INDEX({1,2,3,4,55,66},SUMPRODUCT(($E$3:$E$11=A2)*MMULT($F$3:$K$11,TRANSPOSE(COLUMN(F:K))))-5),0)You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or 2021.
Thanks for your help. I have landed on this code:
=IFERROR(INDEX({1,2,3,4,55,66},MATCH(1,INDEX(F3:K11,MATCH(A2,E3:E11,0),0),0)),"NO MATCH")
Any limitations of this code?
Is it possible to extend this into multiple sheets? please let me know.
=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".
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.
