Index and match to find right range to search

steve1979

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I Have a spread sheet i am stuck on. I have copied a sample of what i am trying to do.
I have a shift roster and am trying to select the shift on shift based on date and what shift is worked. ( Morning or night.) They way i have the formula works but i would like it to select the column it must look at automatically. for example on 1 November it must look for the shift working morning shift and return that shift name. Here is the example
17 oct to 16 november overtime.xlsx
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
2shift 1mmmnnndodododo
3shift 2dododommmnnndo
4shift 3dodododododommmn
5Shift 4nnnndododododom
6
7
8
9
10
11
12date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
13mshift 1shift 1shift 1shift 2shift 2shift 2shift 3shift 3shift 3Shift 4
14N
Sheet5
Cell Formulas
RangeFormula
C1:K1C1=B1+1
B13:K13B13=INDEX($A$2:$K$5,MATCH($A13,B$2:B$5,0),1)
I would like for it to select the column based on date and then find the shift working morning or night. i can help it by selecting the column to use based on date but i would rather have it do it's own look up. for example on the following formula the bold section is what i want to automate instead of selecting each range and entering it manually. copying does not work as some of the dates are possible on a different column/row. =INDEX($A$2:$K$5;MATCH($A13; B$2:B$5 ;0);1)
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Yes, sorry, put the ,0 in the wrong place! As Peter said it should be (translated to semicolons):

=INDEX($A$2:$A$5;MATCH($A13;INDEX($B$2:$K$5;0;MATCH(B$12;$B$1:$K$1;0));0))
 

Some videos you may like

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

steve1979

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
You should be getting it before too long, but I can't be certain.

Try this instead.

20 11 05.xlsm
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
2shift 1mmmnnndodododo
3shift 2dododommmnnndo
4shift 3dodododododommmn
5Shift 4nnnndododododom
6
7
8
9
10
11
12date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
13mshift 1shift 1shift 1shift 2shift 2shift 2shift 3shift 3shift 3Shift 4
14NShift 4Shift 4Shift 4shift 1shift 1shift 1shift 2shift 2shift 2shift 3
Shifts (2)
Cell Formulas
RangeFormula
C1:K1C1=B1+1
B13:K14B13=INDEX($A$2:$A$5,MATCH($A13,INDEX($B$2:$K$5,0,MATCH(B$12,$B$1:$K$1)),0))
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.
17 oct to 16 november overtime.xlsx
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
211-Nov12-Nov13-Nov14-Nov15-Nov16-Nov17-Nov18-Nov19-Nov20-Nov
3shift 1mmmnnndodododo
4shift 2dododommmnnndo
5shift 3dodododododommmn
6Shift 4nnnndododododom
Sheet5
Cell Formulas
RangeFormula
C1:K2C1=B1+1
B2B2=K1+1


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.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Passing 0 as the row number returns the whole column.
 

steve1979

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Yes, sorry, put the ,0 in the wrong place! As Peter said it should be (translated to semicolons):

=INDEX($A$2:$A$5;MATCH($A13;INDEX($B$2:$K$5;0;MATCH(B$12;$B$1:$K$1;0));0))
Both seem to work but i am unsure why. from my untrained eye one formula has an extra 0
from petersss
=INDEX($A$2:$A$5,MATCH($A13,INDEX($B$2:$K$5,0,MATCH(B$12,$B$1:$K$1)),0))
from Rory
=INDEX($A$2:$A$5;MATCH($A13;INDEX($B$2:$K$5;0;MATCH(B$12;$B$1:$K$1;0));0))
I am now thoroughly confused.
according to the Excel helper that pops up when entering the formulas it does not seem as if the columns are used in this formula? am i correct?
If i put 0 as row then the whole row is used? if i put 0 as column then the whole column is used?
THanks for all the help
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

The 0 you highlighted there is specifying an exact match. That may or may not be what you want. Peter's assumes you will want the last date equal to or less than your search date. There is no difference if the dates are always sequential and in ascending order other than that Peter's version is more efficient.
 

steve1979

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
Yes that is what i want, however i have hit a snag... match only seems to work on one row or column at a time... if i have multiple rows with dates in them what can i use in place of match? the shift roster we use has around 14 rows of dates that i would like the formula to work through. here is an example of the roster
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,151
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

You'd probably be better off with a SUMPRODUCT to find the column - something like SUMPRODUCT(($B$3:$AC$16=B$12)*(COLUMN($B$3:$AC$16)))
 

steve1979

New Member
Joined
Nov 4, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
You'd probably be better off with a SUMPRODUCT to find the column - something like SUMPRODUCT(($B$3:$AC$16=B$12)*(COLUMN($B$3:$AC$16)))
17 oct to 16 november overtime.xlsx
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
211-Nov12-Nov13-Nov14-Nov15-Nov16-Nov17-Nov18-Nov19-Nov20-Nov
3shift 1mmmnnndodododo
4shift 2dododommmnnndo
5shift 3dodododododommmn
6Shift 4nnnndododododom
7
8
9
10
11
12
13date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
14m#N/Ashift 1shift 1shift 2shift 2shift 2shift 3shift 3shift 3Shift 4
Sheet5 (2)
Cell Formulas
RangeFormula
C1:K2C1=B1+1
B2B2=K1+1
B14B14=INDEX($A$3:$A$6,MATCH($A14,SUMPRODUCT(($B$1:$K$2=B$13)*(COLUMN($B$1:$K$2))),0))
C14:K14C14=INDEX($A$3:$A$6,MATCH($A14,INDEX($B$3:$K$6,0,MATCH(C$13,$B$1:$K$1)),0))

I tried to implement the change but got a #N/A error. did i use the formula correctly?
=INDEX($A$3:$A$6;MATCH($A14;SUMPRODUCT(($B$1:$K$2=B$13)*(COLUMN($B$1:$K$2)));0))
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
See if this does what you want. I had earlier missed the fact that you use semi-colons instead of commas in your formula syntax but assume that you can make the appropriate substitutions if this does what you want. If not post back for help with that.

You will notice that I have put a mixture of dates in row 13 to show that the formula can find the correct column for that date. I have also assumed that in rows 1 & 2 a particular date will never appear more than once. If it does, then my formula will choose the one that is closest to the left side of the worksheet.

steve1979.xlsm
ABCDEFGHIJK
1date01-Nov02-Nov03-Nov04-Nov05-Nov06-Nov07-Nov08-Nov09-Nov10-Nov
211-Nov12-Nov13-Nov14-Nov15-Nov16-Nov17-Nov18-Nov19-Nov20-Nov
3shift 1mmmnnndodododo
4shift 2dododommmnnndo
5shift 3dodododododommmn
6Shift 4nnnndododododom
7
12
13date01-Nov10-Nov16-Nov04-Nov05-Nov06-Nov19-Nov08-Nov09-Nov11-Nov
14mshift 1Shift 4shift 2shift 2shift 2shift 2shift 3shift 3shift 3shift 1
15nShift 4shift 3shift 1shift 1shift 1shift 1shift 2shift 2shift 2Shift 4
Sheet1
Cell Formulas
RangeFormula
C1:K2C1=B1+1
B2B2=K1+1
B14:K15B14=INDEX($A$3:$A$6,MATCH($A14,INDEX($B$3:$K$6,0,AGGREGATE(15,6,(COLUMN($B$1:$K$1)-COLUMN($B$1)+1)/($B$1:$K$2=B$13),1)),0))



If i put 0 as row then the whole row is used? if i put 0 as column then the whole column is used?
No.
If you put 0 as row then the whole column is used (ie all rows in whatever column is specified)
If you put 0 as column then the whole row is used (ie all columns in whatever row is specified)

Here is an example. The cells summed are the green ones. Column 3 in the range is specified and 0 is used for the rows so it sums values in the whole column (all rows) of column 3 of the specified range.

steve1979.xlsm
EFGH
207886
214818
227939
237276
242531
25
2622
Sheet1
Cell Formulas
RangeFormula
E26E26=SUM(INDEX(E20:H24,0,3))
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,123,393
Messages
5,601,403
Members
414,449
Latest member
Pashtun

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top