Forum Discussion
Index and Match with Multiple Columns and Rows then Return Table Header
- 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),)))
kheldar Your question did mot mention if an employee could be entered in the Names and Groups sheet more than once. I assume NOT!
Since the solution you found involved LET, you should also be able to use XLOOKUP and SEQUENCE. Those, combined with MMULT, some named ranges and two lookup tables can reduce the formulae that find the Group and Shift to this:
=IF(E2<>1,"!!!",XLOOKUP(MAX(MMULT(SEQUENCE(,ROWS(shifts)),--(A2=shifts))),rowidx,rowhdr,,-1,1))and
=IF(E2<>1,"!!!",XLOOKUP(MAX(MMULT(--(A2=shifts),SEQUENCE(COLUMNS(shifts)))),colidx,colhdr))The first part of the formula is merely to warn you if an employee occurs more than once in the Names and Groups sheet.
On Sheet1, the matrix in columns L:U represents the grid from the Names and Groups sheet, where the position of the matching name is highlighted. It's included to help understand what the formulae are doing, but it's not referenced by the formulae. I admit that MMULT isn't the easiest of functions to understand, but using multiple concatenated LET statements with direct cell references isn't all that straight-forward either.