# returning the 2nd instance...

#### kazbear

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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi Kazbear,

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

Excel 2010
AEGJMN
1c
256Delete Itemaf
356Locked Itemb#NUM!
456xc
546d
653e
756f
85
9.
296
Sheet
Cell Formulas
RangeFormula
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

@kazbear

Where do you enter this formula?

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

Replies
7
Views
173
Replies
2
Views
80
Replies
1
Views
76
Replies
8
Views
439
Replies
5
Views
146

1,203,501
Messages
6,055,766
Members
444,821
Latest member
Shameer

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