Forum Discussion
Solved
Index and Match with Multiple Columns and Rows then Return Table Header
Hello, I've got a specific data structure. Bunch of tables with 1 column of data each. What I need to do is to lookup a value in a given range not a column then return its table header. For e...
- I managed to get it done with this formula with the help of a friend.
=LET(LOOKVAL,B2,LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$2:$I$2)*('Names and Groups'!$A$2:$I$14=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$2:$I$2,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$15:$I$15)*('Names and Groups'!$A$16:$I$25=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$15:$I$15,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$26:$I$26)*('Names and Groups'!$A$27:$I$34=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$26:$I$26,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$35:$I$35)*('Names and Groups'!$A$36:$I$42=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$35:$I$35,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$43:$I$43)*('Names and Groups'!$A$44:$I$45=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$43:$I$43,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$46:$I$46)*('Names and Groups'!$A$47:$I$48=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$46:$I$46,FINDVAL),)))
I managed to get it done with this formula with the help of a friend.
=LET(LOOKVAL,B2,LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$2:$I$2)*('Names and Groups'!$A$2:$I$14=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$2:$I$2,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$15:$I$15)*('Names and Groups'!$A$16:$I$25=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$15:$I$15,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$26:$I$26)*('Names and Groups'!$A$27:$I$34=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$26:$I$26,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$35:$I$35)*('Names and Groups'!$A$36:$I$42=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$35:$I$35,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$43:$I$43)*('Names and Groups'!$A$44:$I$45=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$43:$I$43,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$46:$I$46)*('Names and Groups'!$A$47:$I$48=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$46:$I$46,FINDVAL),)))
=LET(LOOKVAL,B2,LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$2:$I$2)*('Names and Groups'!$A$2:$I$14=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$2:$I$2,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$15:$I$15)*('Names and Groups'!$A$16:$I$25=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$15:$I$15,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$26:$I$26)*('Names and Groups'!$A$27:$I$34=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$26:$I$26,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$35:$I$35)*('Names and Groups'!$A$36:$I$42=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$35:$I$35,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$43:$I$43)*('Names and Groups'!$A$44:$I$45=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$43:$I$43,FINDVAL),""))&LET(FINDVAL,SUMPRODUCT(COLUMN('Names and Groups'!$A$46:$I$46)*('Names and Groups'!$A$47:$I$48=LOOKVAL)),IF(FINDVAL,INDEX('Names and Groups'!$A$46:$I$46,FINDVAL),)))