Use INDEX and MATCH to return values from 2 other cells

Good evening all.

I am putting together a book of spreadsheets to be used as scheduling software for restaurant I work at. I am having problems getting formulas worked out correctly to auto-populate Main schedule from sub sheets. Here is what I am needing:

There will be a tab where managers can fill out a Par sheet based on the employee needs for the day. I have it set up so that they can populate the cells based off of each employees' availability. I have attached a copy of the workbook I am working on with the different Sheets. What I am needing is a formula that will find employee name in the "HOH Par" sheet, and return appropriate time and position to the "Main" schedule. Image attached for what I mean. Problem I am having is I can search for the employees' name in the list but I am only able to return one of the values in the adjacent cells. So I am either getting a value of "Prep" or a value of "9a" returned. What I am needing is for it to return "9a Prep". Trying to make it easy for each Department Manager to do their schedules separately and the values be auto-populated into Main schedule for me to review. Help is greatly appreciated with this. If a different function is needed I am all for that as well, I know it may be easier to build the par sheets as tables and possibly use VLOOKUP but I am trying not to have to do that.

Untitled.png

Hi, welcome to the forum

To best describe or illustrate your problem you would be better off attaching a dummy workbook. The workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

To attach a file to your post,
click advanced (next to quick post),
scroll down until you see "manage file",
click that and select "add files" (top right corner).
click "select files" find your file, click "open" click "upload" click 'done" bottom right. click "submit reply"

Once the upload is completed the file name will appear below the input boxes in this window.
You can then close the window to return to the new post screen.

Problem I am having is I can search for the employees' name in the list but I am only able to return one of the values in the adjacent cells. So I am either getting a value of "Prep" or a value of "9a" returned.

My thought is that you may be able to conjunct two Index/Match formulas using an ampersand (&) however if you could show us the formula you are currently using that might help.
Let us know if you have any questions.