You
should be getting it before too long, but I can't be certain.
Try this instead.
It works now i just have to try understand how it works.
Will this work as well if i have more than 1 row of dates? eg.
the way i understand this is as follows (sections in bold):
=
INDEX($A$3:$A$6;(MATCH($A14;INDEX($B$3:$K$6;0;MATCH(B$13;$B$1:$K$1));0));1)
First index is the value of specified cell on row and column.
=INDEX
($A$3:$A$6;(MATCH($A14;INDEX($B$3:$K$6;0;MATCH(B$13;$B$1:$K$1));0));1)
this is the range of results? Why does it only include the shift column with shifts? Is this because they are what i am looking for and because it is "static data" and your index and match do not have to have the same ranges as long as they intersect? ( the match will either say which row or which column)
=INDEX($A$3:$A$6;(
MATCH($A14;INDEX($B$3:$K$6;0;MATCH(B$13;$B$1:$K$1));0));1)
This calculates the row number
=INDEX($A$3:$A$6;(MATCH($A14;INDEX($B$3:$K$6;0;MATCH(B$13;$B$1:$K$1));0));
1)
Selects the column. ( fixed value here as it should be column 1
where i am getting confused is this section for calculating where to look for the correct column:
=INDEX($A$3:$A$6;(
MATCH($A14;INDEX($B$3:$K$6;0;MATCH(B$13;$B$1:$K$1));0));1)
(MATCH(
$A14;INDEX($B$3:$K$6;0;MATCH(B$13;$B$1:$K$1));0)
This is the value that must be matched( found)
(MATCH($A14;
INDEX($B$3:$K$6;0;MATCH(B$13;$B$1:$K$1));0)
second index selects column to look in to find the match?
INDEX($B$3:$K$6;
0;MATCH(B$13;$B$1:$K$1));0)
Why is row number 0? Is it because the final result of the whole formula will be the row?
and as such :INDEX($B$3:$K$6;0;MATCH(B$13;$B$1:$K$1));0)
would mean Look in range $B$3..... row 0, match the value of cell b13 in the range of $B1:$k1 and the 0 for exact match.
Sorry for long winded reply but i need to understand in order to learn and remember.