I have a table with names on the left and corresponding work schedules to the right. I've created a separate table with some of those same names and want it to automatically fill in the corresponding work schedule for that person. Seemed simple but I'm very stuck. My level of experience with Google Sheets is what is stopping me from solving this.

Example Tables:

In the attached picture the table on the top is the original (hardcoded) data. The table on the bottom is where I want the schedule data to be automatically produced based on the name on the left. The fields with #N/A and #ERROR! are both failed formulas I tried. #N/A should have returned B7:G7. #ERROR! should have returned B4:G4.

I tried the 'LOOKUP' function with ARRAYFORMULA(INDEX) hoping to have it look up the value in the column and input the work schedule data that corresponds.

=LOOKUP("Clair",A1:A9,ARRAYFORMULA(INDEX(B1:G9)))

yielded an #N/A.

Started trying to use =If(REGEXMATCH(A13:A21,"Clair"),... ...) but the '... ...' shows where my intellectual limits are at the moment. I couldn't finish it because I think it's the wrong formula to use.

CodePudding user response：

Something like this maybe?

Remove everthing in B13:G17, and put this formula in B13

=BYROW(A13:A17,LAMBDA(NAME,XLOOKUP(NAME,A1:A9,B1:G9,"NOT FOUND")))

BYROW() work with an array row by row, the given data A13:A17 has only 1 column, which is the name of staff as lookup value.

Details: https://support.google.com/docs/answer/12570930?hl=en

XLOOKUP() scan an array for a key value (lookup value), and return another array with corresponding row or col index.

Details: https://support.google.com/docs/answer/12405947?hl=en

CodePudding user response：

Try:

=INDEX(IFNA(VLOOKUP(A13:A17; A1:G10; SEQUENCE(1; 6; 2); )))