Limit Range of INDEX/MATCH based on a Condition

[AllyOops]

Hello I have found many posts that come close to solving my problem but I cant quite crack the answer.

I have a data range that looks like this:

<TABLE style="WIDTH: 488pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=651><COLGROUP><COL style="WIDTH: 136pt; mso-width-source: userset; mso-width-alt: 6619" width=181><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 8009" width=219><COL style="WIDTH: 108pt; mso-width-source: userset; mso-width-alt: 5266" width=144><COL style="WIDTH: 80pt; mso-width-source: userset; mso-width-alt: 3913" width=107><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 136pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=181>Date Produced</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 164pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=219>Status of Data</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 108pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=144>Unique Code</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 80pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=107>Hoping for this</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>14/05/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Current</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>14/05/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Current</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>14/05/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Current</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>14/05/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Current</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=20>14/05/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Current</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>17/04/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Immediate Prior</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Continues</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>17/04/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Immediate Prior</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>B</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Continues</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>17/04/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Immediate Prior</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>C</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Continues</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>17/04/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Immediate Prior</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>D</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Continues</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>17/04/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Immediate Prior</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>E</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Continues</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=20>17/04/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Immediate Prior</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>F</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67>Dropped off List</TD></TR></TBODY></TABLE>


I would like to do a vlookup (I am old school still practising INDEX/MATCH) on only the Immediate Prior Codes and look only in the Current Data to identify if an item has dropped off the list.

Can someone help get me over the line here? I am sure the fact I have hung onto vlookup for too long is part of the problem.

 

[JMonty]

Hello, AllyOops.

Actually, a nicely nested IF function can do this. See if below helps.

Excel 2010
	B	C	D
2	Status	Code	Result
3	Current	A
4	Current	B
5	Current	C
6	Current	D
7	Current	E
8	Immediate Prior	A	Continues
9	Immediate Prior	B	Continues
10	Immediate Prior	C	Continues
11	Immediate Prior	D	Continues
12	Immediate Prior	E	Continues
13	Immediate Prior	F	Dropped Off List
Sheet1

Cell Formulas
Range	Formula
D3	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C3),"")>0,"Continues","Dropped Off List"),"")
D4	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C4),"")>0,"Continues","Dropped Off List"),"")
D5	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C5),"")>0,"Continues","Dropped Off List"),"")
D6	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C6),"")>0,"Continues","Dropped Off List"),"")
D7	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C7),"")>0,"Continues","Dropped Off List"),"")
D8	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C8),"")>0,"Continues","Dropped Off List"),"")
D9	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C9),"")>0,"Continues","Dropped Off List"),"")
D10	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C10),"")>0,"Continues","Dropped Off List"),"")
D11	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C11),"")>0,"Continues","Dropped Off List"),"")
D12	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C12),"")>0,"Continues","Dropped Off List"),"")
D13	=IF($B$3:$B$13="Immediate Prior",IF(IF($B$3:$B$13="Immediate Prior",COUNTIFS($B$3:$B$13,"Current",$C$3:$C$13,$C13),"")>0,"Continues","Dropped Off List"),"")

I used this instead since you've got just one column for the qualifying data. INDEX is best if you have at least two tables, else it becomes an exercise in tweaking.

Let me know if you still want to use INDEX though.

J.

EDIT: The logic here is to check if it's an Immediate Prior first to execute the start of the code, then count how many Immediate Priors with the unique code given have a designated Current status to them. Finally, if the result of the COUNTIF is less than 1, then it returns Dropped Off List. Otherwise, it returns Continues. I hope this is helpful.

 

[AllyOops]

Hi J Monty, this works a treat! I am happy to use the nested if statement over the Index, Many Many Thanks

 

[Robert Mika]

Hi Ally,
Does this do the same job?
Excel 2010<table rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px"><colgroup><col style="background-color: #DAE7F5" width="25px"><col><col><col><col></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th></tr></thead><tbody><tr><td style="color: #161120;text-align: center;">1</td><td style=";">Date Produced</td><td style=";">Status of Data</td><td style=";">Unique Code</td><td style="text-align: right;;"></td></tr><tr><td style="color: #161120;text-align: center;">2</td><td style=";">14/05/2012</td><td style=";">Current</td><td style=";">A</td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">3</td><td style=";">14/05/2012</td><td style=";">Current</td><td style=";">B</td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">4</td><td style=";">14/05/2012</td><td style=";">Current</td><td style=";">C</td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">5</td><td style=";">14/05/2012</td><td style=";">Current</td><td style=";">D</td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">6</td><td style=";">14/05/2012</td><td style=";">Current</td><td style=";">E</td><td style=";"></td></tr><tr><td style="color: #161120;text-align: center;">7</td><td style=";">17/04/2012</td><td style=";">Immediate Prior</td><td style=";">A</td><td style=";">Continues</td></tr><tr><td style="color: #161120;text-align: center;">8</td><td style=";">17/04/2012</td><td style=";">Immediate Prior</td><td style=";">B</td><td style=";">Continues</td></tr><tr><td style="color: #161120;text-align: center;">9</td><td style=";">17/04/2012</td><td style=";">Immediate Prior</td><td style=";">C</td><td style=";">Continues</td></tr><tr><td style="color: #161120;text-align: center;">10</td><td style=";">17/04/2012</td><td style=";">Immediate Prior</td><td style=";">D</td><td style=";">Continues</td></tr><tr><td style="color: #161120;text-align: center;">11</td><td style=";">17/04/2012</td><td style=";">Immediate Prior</td><td style=";">E</td><td style=";">Continues</td></tr><tr><td style="color: #161120;text-align: center;">12</td><td style=";">17/04/2012</td><td style=";">Immediate Prior</td><td style=";">F</td><td style=";">Dropped off List</td></tr></tbody></table>

Sheet1​

<table rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" cellpadding="2.5px" width="85%"><tbody><tr><td style="padding:6px">Worksheet Formulas<table rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB" cellpadding="2.5px" width="100%"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th style=" background-color: #DAE7F5;color: #161120" width="10px">D2</th><td style="text-align:left">=IF(B2="Current","",IF(COUNTIFS($C$2:$C$12,C2)=2,"Continues","Dropped off List"))</td></tr></tbody></table></td></tr></tbody></table>

 

[JMonty]

Thank you, Robert.

Again, Robert corrects me with a much simpler version. The one I presented was created while saying aloud the conditions you wanted, which is why it ended up a bit longer. In addition, you can change the "=2" part of the COUNTIF from Robert's formula to ">1" to check for 2 or more instances.

Let us know if this helps.

J.