Lets say that I want to enter employee names as values into an excel table of date (first column) vs rostered shift (header).

I'd like to be able to autofill a table on another sheet, with date (column) and employee (header), and now the shift as a value.

See linked image for more explanation.

I've tried various combinations of Index, VLookup, Match and Column. In particular, I'm struggling with identifying a row in Table 1 by the "row header" ie the date.

Is there a way to do this within Excel? A way that keeps it to a table structured reference?


I think that I managed to figure it out:

Roster_Sample Conversion Table

With the cells under the employee's names using:


Any further advice or improvements appreciated

  • It sounds like you are looking to use the formulas that will get your result. To help us help you, please add a screenshot of your data and the formulas you tried. Otherwise, it sounds like if you restructured your data, then you could create 2 pivot tables to display your two views... – gns100 Apr 23 at 18:32

enter image description here

How it works:

  • If necessary, Copy Dates from Sheet 1 to sheet 2.
  • Formula in cell AB41 in Sheet 2:



  • Fill formula across.

  • Adjust cell references in the formula as needed.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.