If text appears in a table next to a date, return a value

Hi,

I'm really hoping someone can help with this problem, as I'm stumped with how to even start tackling this.

I have a schedule of engineers attending stores to carry out work. The schedule is created by someone else, and cannot be amended or reorganised in any way, and cannot have any additional columns/rows/formula adding to it. I can add in a new tab however.

What I need is a list of all the engineers, showing which store they're attending on what date.

Here's a rough template of what I have

Engineer calling template.xlsx

On the 2nd sheet, I need the formula from B2:D26 to look at the name in column A, and the Date on row 1, and see if that matches the data on Sheet1. If it does, then I want it to return the store info from Sheet1 colA. I've added in some of the results that are expected.


I'm hoping I've explained this well enough!

hi there. maybe this array formula in E2:

Formula:

Please Login or Register  to view this content.

if you're still using Excel 2003 like what your profile stated, then:

Formula:

Please Login or Register  to view this content.