Index and Match with Multiple Columns and Rows then Return Table Header

[kheldar]

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 example from A2 to I48. So basically, lookup value will return the table header in this structure. Note that all of these are different tables.

I've come across this suggestion but I couldn't implement it to my needs.

Link 

Please note that I'm not an advanced user.

Edit:

turns out I also need the column headers for each group which are not included in the table.

According to this sample, Group needs to return video 2 and shift hour needs to return 10:00

attached a sample file

[#2 · Accepted solution]

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),)))

[#3]

@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.