Lookup formula

njenkins

New Member
Joined
Nov 24, 2012
Messages
23
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.
 

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Re: Lookup formula help

Hi, see if you can adapt something like this to your actual set-up - the formula in B2 can be copied down and across.


Excel 2013/2016
ABCDE
1Initial >FR
201-JanJob 1Job 3Job 6
302-JanJob 2
403-Jan
504-JanJob 3Job 4
605-Jan
706-Jan
807-Jan
908-Jan
1009-Jan
1110-Jan
1211-Jan
1312-Jan
1413-Jan
Sheet2
Cell Formulas
RangeFormula
B2=IFERROR(INDEX(Sheet1!$B$1:$L$1,AGGREGATE(15,6,(COLUMN(Sheet1!$B$1:$L$1)-COLUMN(Sheet1!$B$1)+1)/(INDEX(Sheet1!$B$2:$L$14,MATCH($A2,Sheet1!$A$2:$A$14,0),0)=$B$1),COLUMNS($B2:B2))),"")




Excel 2013/2016
ABCDEFGHI
1DateJob 1Job 2Job 3Job 4Job 5Job 6Job 7Job 8
201-JanFRRBFRRBXXFR
302-JanFRXX
403-Jan
504-JanFRFRCC
605-Jan
706-Jan
807-Jan
908-Jan
1009-Jan
1110-Jan
1211-Jan
1312-Jan
1413-Jan
Sheet1
 
Upvote 0
Re: Lookup formula help

thats not working
this is a copy of my worksheet
[TABLE="width: 2201"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col span="3"><col><col><col><col></colgroup><tbody>[TR]
[TD] [/TD]
[TD]joba[/TD]
[TD]jobb[/TD]
[TD]jobc[/TD]
[TD]jobd[/TD]
[TD]jobe[/TD]
[TD]jobf[/TD]
[TD]jobg[/TD]
[TD]jobh[/TD]
[TD]jobi[/TD]
[TD]jobj[/TD]
[TD]jobk[/TD]
[TD]jobl[/TD]
[TD]jobm[/TD]
[TD]hobn[/TD]
[TD]jobm[/TD]
[TD]jobo[/TD]
[TD]jobp[/TD]
[TD]jobq[/TD]
[TD]jobr[/TD]
[/TR]
[TR]
[TD="align: right"]27-Feb[/TD]
[TD]JS[/TD]
[TD]JScr[/TD]
[TD]EM[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD]NJ[/TD]
[TD]JS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28-Feb[/TD]
[TD]JS[/TD]
[TD]JScr[/TD]
[TD]EM[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EM[/TD]
[TD]no clinic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]01-Mar[/TD]
[TD]EM/JScr[/TD]
[TD]JScr[/TD]
[TD]EM[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JScr[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]02-Mar[/TD]
[TD]JS[/TD]
[TD]JScr[/TD]
[TD]EM[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Bal[/TD]
[TD]MD[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]03-Mar[/TD]
[TD]JS[/TD]
[TD]JScr[/TD]
[TD]EM[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]04-Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]05-Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]06-Mar[/TD]
[TD]JS[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD]NJ[/TD]
[TD]JS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]07-Mar[/TD]
[TD]JS[/TD]
[TD]EM[/TD]
[TD]EM[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EM[/TD]
[TD]no clinic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]08-Mar[/TD]
[TD]EM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]09-Mar[/TD]
[TD]JS[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AW[/TD]
[TD]EM[/TD]
[TD][/TD]
[TD][/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10-Mar[/TD]
[TD]EM for JS[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11-Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JScr[/TD]
[/TR]
[TR]
[TD="align: right"]12-Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JScr[/TD]
[/TR]
[TR]
[TD="align: right"]13-Mar[/TD]
[TD]EM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD]NJ[/TD]
[TD]JS[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]14-Mar[/TD]
[TD]EM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JS[/TD]
[TD]no clinic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]15-Mar[/TD]
[TD]AM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JScr[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]16-Mar[/TD]
[TD]JS[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JScr[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]17-Mar[/TD]
[TD]JS[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]18-Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]19-Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]20-Mar[/TD]
[TD]EM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD]JScr[/TD]
[TD]no clinic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]21-Mar[/TD]
[TD]EM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AW[/TD]
[TD]no clinic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]22-Mar[/TD]
[TD]EM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EM[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]JScr[/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]23-Mar[/TD]
[TD]EM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]EM[/TD]
[TD]MD[/TD]
[TD][/TD]
[TD][/TD]
[TD]AW[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]24-Mar[/TD]
[TD]EM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]25-Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]26-Mar[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]27-Mar[/TD]
[TD]AW[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD]JScr[/TD]
[TD]no clinic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]28-Mar[/TD]
[TD]JScr[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AW[/TD]
[TD]no clinic[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]29-Mar[/TD]
[TD]AM[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AM[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]30-Mar[/TD]
[TD]MD[/TD]
[TD]JScr[/TD]
[TD]AW[/TD]
[TD]NJ[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]AW[/TD]
[TD]JScr[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Re: Lookup formula help

thats not working

Hi, if you are not able to adapt the suggestion to your layout then you will need to either describe the ranges involved or post sample data that demonstrates what the ranges involved are. i.e. the column and row references. We will also need to see the layout and expected results for the "Individual" sheet.
 
Upvote 0
Re: Lookup formula help

as what I’ve done you presumably cannot see

I can see your data, I can't tell though from that what columns and rows are what and I can't see sample data of the expected results sheet.

I posted my tables in post 2 using the "MrExcel HTML Maker", details can be found here along with some other options.

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Note, "Not working" does not give us much of a hint to help you diagnose what is going wrong. When you re-post your sample data (including the expected results), also post the formula you tried and describe in what way it didn't work.
 
Upvote 0
Re: Lookup formula help

I’ll try that.
I cut/pasted the formulae from your cell and it returned no value “”
 
Upvote 0
Re: Lookup formula help

i started again from scratch and did get it to work. many thanks
presumably for a wider and longer worksheet i just adjust the 14 to the number of rows i have and the L to the number of columns i have
i am not sure of the relevance of the 15,6 (num_function, options) to an enlarged worksheet
 
Last edited:
Upvote 0
Re: Lookup formula help

Great, thanks for letting us know :)

presumably for a wider and longer worksheet i just adjust the 14 to the number of rows i have and the L to the number of columns i have

Yes, that's correct.

i am not sure of the relevance of the 15,6 (num_function, options) to an enlarged worksheet

These tell the Aggregate() function what to do, they do not need to be changed.
 
Upvote 0
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"
 
Upvote 0

Forum statistics

Threads
1,223,888
Messages
6,175,212
Members
452,618
Latest member
Tam84

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top