Lookup formula

[njenkins]

I have a worksheet with dates in the first column and header rows are jobs to do. Each row has initials of people set to do those jobs. There may be more than one job per person - shown as that persons initial being in more than one column in any particular row.

The sheet is not easy to read for an individual person. I’d like a formula to create an individualised sheet for each person showing their jobs each day.

The worksheet would probably start with dates down the initial column - then a lookup formula pointing to the original worksheet and the row of the corresponding date. It would then look for cells with that persons initials in, and show the corresponding header row to that cell. It would need to cope with there potentially being several or no instances of the initials in any row.

 

[FormR]

Re: Lookup formula help

btw is there any way it could be adapted to included a cell in the initial worksheet which contains the two sets of initials eg. "FR and NJ" rather than just included those with just "FR"

It would be easier if those two sets of initials were in separate cells. Can you work with that?

Can you post the working formula that you now have and let us know which cells will contain the sets of initials?

 

[njenkins]

Re: Lookup formula help

the formula is
=IFERROR(INDEX(Sheet1!$B$1:$T$1,AGGREGATE(15,6,(COLUMN(Sheet1!$B$1:$T$1)-COLUMN(Sheet1!$B$1)+1)/(INDEX(Sheet1!$B$2:$T$140,MATCH($A2,Sheet1!$A$2:$A$140,0),0)=$B$1),COLUMNS($B2:B2))),"")

so, at present the cells might contain one set of initials, eg "BF", or they could contain two sets of initials "BF or NJ" where a job has not yet been properly allocated.

 

[FormR]

Re: Lookup formula help

at present the cells might contain one set of initials, eg "BF", or they could contain two sets of initials "BF or NJ" where a job has not yet been properly allocated.

So it's the cells in sheet1 that may contain two sets of initials?

If so, one option:

=IFERROR(INDEX(Sheet1!$B$1:$T$1,AGGREGATE(15,6,(COLUMN(Sheet1!$B$1:$T$1)-COLUMN(Sheet1!$B$1)+1)/(ISNUMBER(SEARCH($B$1,INDEX(Sheet1!$B$2:$T$140,MATCH($A2,Sheet1!$A$2:$A$140,0),0)))),COLUMNS($B2:B2))),"")