Return machine number based on employee name and date

Copper Contributor

I am trying to return a machine number that an employee worked on for a given date using a spreadsheet that gets each days activities added into it. On the Daily Empl. Effic. tab I am looking to return the machine in cell B2 based on cell A2 and F1 from Daily Empl. Effic. tab using Monthly Machine Data tab to get the result. In the Monthly Machine Data tab the information is in columns H (employee name), O (date of production) and L (machine) which is the value I want to return. I have tried Index & Match a few different ways and also Lookup which you can see in column B, rows 3, 4, 6, 7 and 8.





1 Reply

@Scott Hetzel 

Thank you for your support! I've changed the layout of the dates in the Monthly Machine Data tab with the help of Flash Fill. I've copied the dates from column O and pasted them into column AR and then applied Flash Fill in column AS. Here is a description how to apply Flash Fill.

Using Flash Fill in Excel - Microsoft Support


In the file the calculation of the formulas is currently set to manual. The screenshot shows where you can change the calculation options. Formulas -> Calculation options -> Automatic (or in the screenshot in german Excel: Formeln -> Berechnungsoptionen -> Automatisch).

calculation options.JPG

You can then enter the formula in cell B3 and fill it down to B10 and the result is returned immediately.