Horizontal Lookup for date, vertical lookup for value then output list of names that match

**sadsack5000** · 09-03-2020, 08:58 AM

Hi Everyone,

I've got a brain cruncher here for you if you please

I am using a matrix to track personnel movements. Dates across the top, names down the left hand side. The values in in the matrix correspond to where someone is e.g. 1=Office, 2=Field, 3=Home

See picture below

Data.png

It works well for tracking the locations of a large team however i am looking to make some summary tables to show a list of names as to who is, for example, in the office TODAY.
So i need something that will look across the top to find todays date then go down and list all the names that have a "1" entry

I have been googling and playing with complex array formulas which are way outside my knowledge

Hope someone can help. Reply if you need more information

**CK76** · 09-03-2020, 09:23 AM

Try...
=INDEX($A$2:$A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-1)/(INDEX($B$2:$Q$11,,MATCH(TODAY(),$B$1:$Q$1,0))=1),ROWS($A$1:A1)))

Copy down.

If you don't want to see error value. Nest it in IFERROR(formula,"")

**Fluff13** · 09-03-2020, 09:25 AM

How about
In S2 copied down
=IFERROR(INDEX($A$2:$A$11,AGGREGATE(15,6,(ROW($A$2:$A$11)-ROW($A$2)+1)/(($B$1:$Q$1=TODAY())*($B$2:$Q$11=1)),ROWS(S$2:S2))),"")

**sadsack5000** · 09-03-2020, 09:49 AM

What!?!?! thats impossible i've been messing around with this thing for days and you guys both solve it in MINUTES

Both your answers work so thankyou very much to both of you

And here i was hoping i had something that would really test your skills

Let me have a play and i may come back with an even difficult brain cruncher for you to expand on my list

**Fluff13** · 09-03-2020, 09:50 AM

Glad to help & thanks for the feedback.

**sadsack5000** · 09-04-2020, 08:58 AM

OK next level of difficulty

1. Supposing i want a list of all people in the office AND field in S2, how can i modify the above equation to lookup both 1's AND 2's? (and maybe a 3rd or 4th value i havent defined here)
2. Using the same listing method, how could i use column T2 to explain where they are e.g. All 1's become "Office" and all 2's become "Field".

My actual spreadsheet is obviously way more complicated than my screenshot and i have managed to manipulate your original equation to lookup other data of people in work e.g. role. The 1's, 2's etc are not lookup values though so i cant figure out how to make it translate/work

Thanks in advance excel gurus

**CK76** · 09-04-2020, 09:21 AM

I'd strongly recommend uploading sample workbook. That mimics your actual set up.

From your description, it isn't clear to me how your workbook is set up.

**sadsack5000** · 09-04-2020, 09:57 AM

Apologies i cant post the actual spreadsheet so i have included a mock up dummy

If you check out the summary tab im trying to get the parts i've filled in yellow and red

For the list of names im just looking to include several work locations but not all e.g. 1, 2 and 3 (maybe include an OR equation in your big equation above)
For the location i looking for it to spell out where each person is on that day based on the number and description from the legend (e.g. 1="Office")

Sorry if this is not clear, let me know if you dont follow

**CK76** · 09-04-2020, 10:52 AM

If number is 1 to 3. Then it's simply changing "=1" to "<=3". And also, offset of "-1" should be "-2".

If using OR condition. I'd go with Fluff13's formula construct.
Ex: 1 or 2.
=IFERROR(INDEX(Schedule!$A$3:$A$20,AGGREGATE(15,6,(ROW($A$3:$A$20)-ROW($A$3)+1)/((Schedule!$C$2:$AG$2=TODAY())*((Schedule!$C$3:$AG$20=1)+(Schedule!$C$3:$AG$20=2))),ROWS(S$2:S2))),"")

As for location...
=IFERROR(LOOKUP(INDEX(Schedule!$C$3:$AG$20,MATCH(A6,Schedule!$A$3:$A$20,0),MATCH($B$3,Schedule!$C$2:$AG$2,0)),Schedule!$J$22:$J$29,Schedule!$K$22:$K$29),"")

See attached.

**sadsack5000** · 09-05-2020, 04:51 AM

Excellent! It took me some time to map the equations to my proper spreadsheet but both new equations work a treat

I have discovered 2 problems though:
1. The first equation from Fluff although working excellent tends to make excel slow down when users are entering numbers in the schedule matrix. "Calculating (4 Threads) %XX" appears at the bottom and it makes it very painful for users to input several days... number... wait... number... wait... etc. I stripped the spreadsheet of all equations to isolate the problem to this equation. On my real spreadsheet i have around 150 names and about 2 years of dates going across the top. I know full well people wont fill it in with this slowdown. Searching this forum i found that some equations can slow down excel. Is there anything you can see that could be causing this grind? I even downloaded the spreadsheet to my local drive (rather than over a network) and its still just as grindy.

2. Im not sure if this one can be fixed but in my testing i've found that the list of names, roles and locations generate fine (albeit with the grind above) and users can then go and type their days activities in "Task". This works well until another person changes the schedule i.e. adds a 1 to signify he/she is at work. This will automatically refresh the list of names on the summary page but task comments do not move when names move. We generally have people update the sheet every day by 9am each morning and then review in a team meeting. However with people going in and out of the sheet since 6am the Task comments will get muddled up fast before the leadership review. Like i said im not sure this is fixable in excel alone.

Any ideas?

**oeldere** · 09-05-2020, 05:57 AM

With the code below to change the format of the data.

Please Login or Register  to view this content.

Make a table of the data on sheet output

Insert => table

E1 =RIGHT(B2,1)

F1 =IF($C2=$I$1,"yes","")

I1 = 5-9-2020 (inputcell for date)

After that

filter on column E for the group and on column F for the criteria yes.

See the attached file.

**sadsack5000** · 09-06-2020, 02:07 AM

Hi thankyou for your assistance but im sorry what you've typed is really complex and im struggling to understand it but from the sample spreadsheet you've attached it doesnt look like this solution will be "Management Friendly".

**oeldere** · 09-06-2020, 04:29 AM

How about this one with a graph.

See the attached file (sheet Pivot Table).

**sadsack5000** · 09-08-2020, 02:30 AM

Hi once again i appreciate the assistance but im just after simple. Two tabs, summary and schedule. I disabled automatic calculation as a workaround to problem 1 with the calculating threads but its not ideal.
Still not sure how to solve problem 2

**sadsack5000** · 09-10-2020, 12:10 PM

Ok so i manipulated my spreadsheet to make the summary sheet calculate manually with a button to refresh. I also took out the task column as it seemed it might be too complicated to control with people changing their schedules at different times.

I have added two extra columns though which i could use some help with please? @CK76

I now have a column for first date on shift and last day on shift. The idea is for the equations to look back through the numbers to see a change, this represents the first day on shift then look forward to the next change in numbers which will represent the last day on shift.

Is this possible?

This will be the last time i'll ask for something on this topic, i've probably more than outstayed my welcome

**JeteMc** · 09-13-2020, 05:09 PM

If I understand correctly then I feel that the following will yield what you want:
For D6 and down:

Formula:

Please Login or Register  to view this content.

For E6 and down:

Formula:

Please Login or Register  to view this content.

For F6 and down:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**sadsack5000** · 09-14-2020, 11:07 AM

Hi JeteMc,

Thanks for your help. I tried your formulas on my test spreadsheet, they work but i dont think i gave you all of the information you need. You see the work schedule is MUCH bigger and contains the shift patterns of hundreds of people. The summary page on my is to help management quickly see who is on shift, when they come on and when they go off. See updated attachment, even this is tiny in comparison to the real sheet but its key to understand that people work multiple shifts a year.

Theres two things i spotted:

1) It looks as if formula 1 and 2 plot out the start and end dates of a single shift in a given dataset. In reality our guys work many shifts a year, usually on regular intervals e.g. 14 days on/off or 28 days on/off but sometimes they can work much longer swings (like now during COVID). The intention of these two formulas is to summarise the start date and end date of the current shift. In other words the formulas need to somehow discover the start date and end date around TODAY()s date. Not sure if that makes sense?

2) I also noticed that formula 1 and 2 only lookup only "1" values. Is there any way to treat 1, 3, 4 or 7 numbers equally? e.g. 3 3 3 3 3 1 1 1 1 would be counted as 8 days straight. Maybe an OR statement in there somewhere

**JeteMc** · 09-14-2020, 04:51 PM

As to point #2, I am confused as to some of the values to include/exclude. For example 7 is listed as 'sick' on the Schedule sheet. Also 2, 'field', is not mentioned although several persons are listed as location 'field' on the Summary sheet.
It almost seems that what is needed is values <=5.
That said a way to modify formula 1 to include 1,3,4 and 7 is:

Formula:

Please Login or Register  to view this content.

Formula 2 would be similar except the INDEX(15 would be INDEX(14 instead.
I am not ignoring point #1, just wanting to make sure I understand point #2 before proceeding.
Let us know if you have any questions.

**JeteMc** · 09-14-2020, 09:35 PM

Looking at this a bit more if the values to be included could be grouped then the formula that would account for point #1, for Date On could be:

Formula:

Please Login or Register  to view this content.

The formula for Date Off could be:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**sadsack5000** · 09-15-2020, 01:48 AM

Hi JeteMc,

Ahhhhhhh the number designations and work location descriptions are different in my main spreadsheet, i should've just copied the locations like for like but i rushed the test sheet out for this thread.
In my master sheet 1, 3, 4 and 7 represent different work locations, i've updated this in the attachment below.
I see you've shown me how to do change the formula for any numbers though so that's great. Confirmed the updated formula is what im after.

**sadsack5000** · 09-15-2020, 02:14 AM

When you say grouped do you mean use for example 1,2,3,4 for work days instead of 1,3,4,7 ?
That's not impossible but ill have to change the schedule data for all staff and days to match a new legend.
Let me know if thats what you want me to do.

I tried the updated formulas for date on and date off but i think there's some bugs in them. I think there was a missing sheet reference in the first COLUMN(C$2:CE$2 which i was able to fix but wasnt sure where COLUMN(B$2) is supposed to be referencing

**JeteMc** · 09-15-2020, 09:06 AM

Try the following:
For Days On:

Formula:

Please Login or Register  to view this content.

For Days Off:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

**sadsack5000** · 09-17-2020, 05:43 AM

Originally Posted by JeteMc

Try the following:
For Days On:

Formula:

Please Login or Register  to view this content.

For Days Off:

Formula:

Please Login or Register  to view this content.

Let us know if you have any questions.

Hi JeteMc

What is the below red part of the formula referencing? I cant figure out if that is supposed to be B3 on the summary sheet (todays date) or B2 on the schedule page (which would make it the "Role" heading??).
=IF(A6="","",INDEX(Schedule!C$2:CE$2,AGGREGATE(14,6,(COLUMN(C$2:CE$2)-COLUMN(B$2))/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>1)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>3)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>4)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>7)/(Schedule!C$2:CE$2<=B$3),1)+1))

Also, should this part:
=IF(A6="","",INDEX(Schedule!C$2:CE$2,AGGREGATE(14,6,(COLUMN(C$2:CE$2)-COLUMN(B$2)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>1)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>3)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>4)/(INDEX(Schedule!C$3:CE$20,MATCH(A6,Schedule!A$3:A$20,0),)<>7)/(Schedule!C$2:CE$2<=B$3),1)+1))

Read (COLUMN(Schedule!C$2:CE$2) ?

Just checking before i apply....

**JeteMc** · 09-17-2020, 11:22 AM

...(COLUMN(C$2:CE$2)-COLUMN(B$2))... is producing an array based on the number columns being indexed. So you really don't need to change it to (COLUMN(Schedule!C$2:CE$2)-COLUMN(Schedule!B$2)) as there is no need to have the formula to reference columns on a particular sheet in order to produce the array. Utilizing the Evaluate Formula feature on the Formulas sheet may help in understanding what this part of the formula is doing.
Let us know if you have any questions.

**sadsack5000** · 09-17-2020, 12:16 PM

Ok so i built the formulas into my spreadsheet and it works perfectly!

Thankyou so much for your help JeteMc, i would never have been able to write something as complex as this. Just looking at the equations melts my brain

Attaching my finished product here for others to use as a template for managing a teams rotation schedule.

Hopefully mgmt wont ask for any other "features"

**JeteMc** · 09-17-2020, 02:30 PM

You're Welcome, thank you for the feedback and for including a file. Please take a moment to mark the thread as 'Solved' using the thread tools menu above your first post. I hope that you have a blessed day.

Horizontal Lookup for date, vertical lookup for value then output list of names that match

LinkBack

Thread Tools

Rate This Thread

Display

Horizontal Lookup for date, vertical lookup for value then output list of names that match

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Re: Horizontal Lookup for date, vertical lookup for value then output list of names that m

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] lookup if my lookup array with my data position is vertical and horizontal

[SOLVED] Lookup with THREE conditions: one vertical, two horizontal

[SOLVED] VBA Vertical and horizontal Lookup ???

[SOLVED] Horizontal and Vertical Lookup

[SOLVED] Lookup a vertical and horizontal value

Horizontal and Vertical lookup.

Lookup from a Horizontal Table, not Vertical List

Bookmarks

Bookmarks

Posting Permissions