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,

Mechanics

Shift

5A

6A

7A

8A

9A

10A

11A

12P

1P

2P

3P

4P

5P

6P

7P

8P

9P

10P

11P

12A

1A

2A

3A

4A

Name 1

1

X

X

X

X

X

X

X

X

X

X

Name 2

1

X

X

X

X

X

X

X

X

X

X

Name 3

1

X

X

X

X

X

X

X

X

X

X

Name 4

1

X

X

X

X

X

X

X

X

X

X

Name 5

1

X

X

X

X

X

X

X

X

X

X

Name 6

2

X

X

X

X

X

X

X

X

X

X

Name 7

2

X

X

X

X

X

X

X

X

X

X

Name 8

2

X

X

X

X

X

X

X

X

X

X

Name 9

2

X

X

X

X

X

X

X

X

X

X

Name 10

2

X

X

X

X

X

X

X

X

X

X

Name 11

3

X

X

X

X

X

X

X

X

X

X

Name 12

3

X

X

X

X

X

X

X

X

X

X

Name 13

3

X

X

X

X

X

X

X

X

X

X

Name 14

3

X

X

X

X

X

X

X

X

X

X

Name 15

3

X

X

X

X

X

X

X

X

X

X

<tbody>
</tbody>

</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.