returning the 2nd instance...

[kazbear]

I am using this formula to match results (It works great):
=INDEX('sheet'!$A$2:$J$1293,MATCH(1,('Sheet'!$A$2:$A$1293=$E$8)*('Sheet'!$E$2:$E$1293=A29)*('sheet'!$G$2:$G$1293<>"Delete Item")*('sheet'!$G$2:$G$1293<>"Locked Item"),0),10)

Most of the time there will be only one result. But I do have a couple of instances where there will be more than one result. I would like to return those values as well.

The cell value I am capturing is text.

Is there a way I can modify this formula to return the second (or third, etc...) match?

 

[LloydFinancials]

Hi Kazbear,

I propose the following. Let me know if you have any questions.

Excel 2010
	A	E	G	J	M	N
1						c
2	5	6	Delete Item	a		f
3	5	6	Locked Item	b		#NUM!
4	5	6	x	c
5	4	6		d
6	5	3		e
7	5	6		f
8		5
9		.
29	6
Sheet

Cell Formulas
Range	Formula
N1	{=INDEX($A$2:$J$1293,SMALL(IF((Sheet!$A$2:$A$1293=$E$8)*(Sheet!$E$2:$E$1293=$A$29)*($G$2:$G$1293<>"Delete Item")*($G$2:$G$1293<>"Locked Item"),ROW($A$2:$A$1293)-MIN(ROW($A$2:$A$1293))+1,""),ROWS(1:$1)),10)}
Press CTRL+SHIFT+ENTER to enter array formulas.

'Great day,

Luke

 

[Aladin Akyurek]

@kazbear

Where do you enter this formula?

 

[kazbear]

Works perfect.
Thank you. I should be able to use this same concept now for the other areas where I have similar needs...

 

[LloydFinancials]

Great! Thanks for the reply. I am glad I could help.