Dec 29 2021 05:58 PM - edited Dec 29 2021 06:33 PM
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.
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
Dec 29 2021 08:30 PM
SolutionDec 30 2021 12:31 AM
@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.
Dec 29 2021 08:30 PM
Solution