I am trying to help out the really nice admin lady for the work christmas orders and run into a bit of a road block. I'm trying to find a way to get the top row text if a person has marked their order cell with a 1. eg;

The end result should have the name, their meal, salad, desert and date of their next shift. to be formatted something like this..

I'm used to doing a bunch of vlookups but don't think it would work in this scenario. I have a list of about 300 employees so thought a formula might be easier. you'll be saving christmas!

CodePudding user response：

This is assuming A1 orientation on both pictures and Sheet1 is the first picture and Sheet2 is the second picture:

=INDEX(Sheet1!$B$1:$I$1,MATCH("1",INDEX(Sheet1!$B:$I,MATCH($B3&" "&$C3,Sheet1!$A:$A,0),0),0))

For each section change the Bs and Is on the Sheet1 references to encompass only those columns that pertain to the type of food.