How to get top row cell text from non blank row cells

user_9308162 0
Viewed 42

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;

enter image description here

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

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!

excel-formula excel
1 Answers

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.

user_48515900
Related