# Index Match Function

#### baker_89

##### New Member
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..

### Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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.

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>

Last edited:
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:

Code:
``[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.

Last edited:
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.

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!!

Replies
3
Views
273
Replies
7
Views
312
Replies
2
Views
278
Replies
5
Views
254
Replies
9
Views
268

### Forum statistics

1,203,224
Messages
6,054,237
Members
444,711
Latest member
Stupid Idiot ### 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.

### Which adblocker are you using?    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

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