Index Match Function

[baker_89]

I am having trouble getting this formula to work properly.


=INDEX(SetUp!$C$2:$Z$16,MATCH($O50,SetUp!$A$2:$A$16,0),MATCH(P$1,SetUp!$C$1:$Z$1,0))

On the sheet the formula is in I have data from row P2:DV2.

P2 is a formula =MIN('Scheduling Setup'!C2:F2)) to get the earliest time available between two cells
P3 and on is a formula =IFERROR(IF(P2+TIME(1, 0, 0)<max('scheduling setup'!$c$3:$f$3),p2+time(="" 1,="" 0,="" 0),="" ""),="" "")="" to="" add="" one="" hour="" until="" the="" greatest="" time="" available="" from="" another="" cell="" create="" a="" window="" of="" time.
P1 and on is a helper formula to convert the time in P3 to "hA/P"
O3:O113 is a list of names that vary in order


On Sheet "SetUp"

A2:A16 has a Master List of names in the same format and spelling used on the other sheet
C1:Z1 is a list of times from 5A-4A formatted as "hA/P"
C2:Z16 is a grid with "X" in a cell that corresponds with the time that name is on shift during a single workday



I am trying to get the formula to pull the "X" over into the appropriate cell to essentially create a simple GANNT chart to show time of availability.


That seems like headache to think about, can't post a preview of the sheet.....

Hope i explained it well enough to make sense..

Thanks in advanced.</max('scheduling>

 

[bobsan42]

The formula is 100% good. The data remains to be checked.
What do you mean by "to work properly"?
Normally when a more complicated formula fails you have to break it down into simpler pieces and see which one fails, then look for the problem.
What are you trying to match: what is in $O50 and what are the values in SetUp!$A$2:$A$16, what is in P$1 and what are the values in SetUp!$C$1:$Z$1?
In the formula there is no reference to P2 or P3 so they are irrelevant for the moment.

 

[baker_89]

Thank you for the response, I apologize for the delay in the my response.

Column O is a formula but is a name that it is referencing from SetUp!$A$2:$A$16. (Formula is just a simple helper column to pull data from another sheet, Sheet1!D#)

Row 1 (P1 and on) is another helper column to get the date from Row 2 (P2 and on) to a "hA/P" format (6a) for a time which is the same format in SetUp!$C$1:$Z$1



"SetUp" Sheet tab is has a list of names in column A and those times in Row 1. The data in the rest is just an x to signify when they are on or off shift according to the times in Row 1.

I want to take that "x" on my "Scheduler" Sheet tab and use it in a conditional format to change the cell color if the person is on shift in P3:DV112. the times in that area can go over 24 hours.


<max('scheduling setup'!$c$3:$f$3),p2+time(="" 1,="" 0,="" 0),="" ""),="" "")="" it="" is="" taking="" two="" times="" and="" filling="" in="" every="" hour="" available="" between="" those="" times.
<max('scheduling setup'!$c$3:$f$3),p2+time(="" 1,="" 0,="" 0),="" ""),="" "")<max('scheduling="" "")<font="" color="#333333"><max('scheduling setup'!$c$3:$f$3),p2+time(="" 1,="" 0,="" 0),="" ""),="" "")
<max('scheduling setup'!$c$3:$f$3),p2+time(="" 1,="" 0,="" 0),="" ""),="" "")***

SetUp Sheet is like this,



[TABLE="width: 1036"]
<tbody>[TR]
[TD]Mechanics[/TD]
[TD]Shift[/TD]
[TD]5A[/TD]
[TD]6A[/TD]
[TD]7A[/TD]
[TD]8A[/TD]
[TD]9A[/TD]
[TD]10A[/TD]
[TD]11A[/TD]
[TD]12P[/TD]
[TD]1P[/TD]
[TD]2P[/TD]
[TD]3P[/TD]
[TD]4P[/TD]
[TD]5P[/TD]
[TD]6P[/TD]
[TD]7P[/TD]
[TD]8P[/TD]
[TD]9P[/TD]
[TD]10P[/TD]
[TD]11P[/TD]
[TD]12A[/TD]
[TD]1A[/TD]
[TD]2A[/TD]
[TD]3A[/TD]
[TD]4A[/TD]
[/TR]
[TR]
[TD]Name 1[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/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]Name 2[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/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]Name 3[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/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]Name 4[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/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]Name 5[/TD]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/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]Name 6[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 7[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 8[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 9[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 10[/TD]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Name 11[/TD]
[TD]3[/TD]
[TD]X[/TD]
[TD]X[/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]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Name 12[/TD]
[TD]3[/TD]
[TD]X[/TD]
[TD]X[/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]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Name 13[/TD]
[TD]3[/TD]
[TD]X[/TD]
[TD]X[/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]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Name 14[/TD]
[TD]3[/TD]
[TD]X[/TD]
[TD]X[/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]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
[TR]
[TD]Name 15[/TD]
[TD]3[/TD]
[TD]X[/TD]
[TD]X[/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]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[/TR]
</tbody>[/TABLE]


</max('scheduling></max('scheduling></max('scheduling></max('scheduling>

 

[bobsan42]

I got utterly confused trying to follow through, but maybe it's because a lot is on my mind today.
Can you upload the file somewhere and post a link to it?
And what exactly do you mean by not working properly?

Afterthought: As far as I understand you are trying to match time vs. date. Be careful with this - matching the format (6A) doesn't match the values.
Making 2 date/time values look the same is not like making the values the same - this is a good reason to have no match. 10:00:00 is not the same like 15.03.2019 10:00:00

If the time values in Setup sheet have no date portion, while the value in P1 has - then maybe try the formula like this:

[COLOR=#333333]=INDEX(SetUp!$C$2:$Z$16,MATCH($O50,SetUp!$A$2:$A$16,0),MATCH(MOD(P$1,1),SetUp!$C$1:$Z$1,0))[/COLOR]

But you will also have to have the times identical - 10:00:05 will not match 10:00:00 even if they look the same in format hA/P.

The other way is to make the times as text and then match them: e.g. in P1 you can put =Format(P2,"hA/P"), and something similar in setup sheet.
Also check if the time values in Setup sheet are actually values and not text.

 

[bobsan42]

So as i think I said earlier - the formula problem comes most probably from a data mismatch - so focus on this.
AND AGAIN: split the formula to pieces (check primarily the time MATCH) and see which one doesn't match.

 

[baker_89]

So as i think I said earlier - the formula problem comes most probably from a data mismatch - so focus on this.
AND AGAIN: split the formula to pieces (check primarily the time MATCH) and see which one doesn't match.

It was indeed an issue with data mismatch, formatted the times to a text format and it worked. Had to add an IF statement with it to get the conditional format piece working.

Final Result was,
=IF(INDEX(SetUp!$C$2:$Z$16,MATCH($O3,SetUp!$A$2:$A$16,0),MATCH(P$1,SetUp!$C$1:$Z$1,0))="x",1,0)


Thank you for the help!!

 

[bobsan42]

Glad I could help.