return multiple matches using INDEX and SMALL

**merlyn45** · 01-29-2013, 05:24 PM

Hello All,
I am trying to develop a formula to search for certain criteria and return all instances. I need to search col E (# of days until review is due) and if it fits criteria like "between 15 and 30 days" returns the employees name from col A, managers name from D, and the # of days until review is due from col E into another sheet call "Due for Review". There may be multiple matches and there also may be negative numbers in col E because the review is overdue. I have attached an example workbook. I have tried multiple formulas at the bottom of the "Indirect" sheet just to get something to work....using an array of INDEX, SMALL, and ROW and did get it to work if matching a specific #, like 30. Here is what I tried: {=IFERROR(INDEX($A$5:$E$15,SMALL(IF($E$5:$E$15=$A$109,ROW($A$5:$A$15)-4),ROW(A1)),1),"")}. I also cant figure out how to return the instances that are in a range, like 15 to 30, or less than 7, and how to return the multiple cells like manager name and # of days until due....Any Ideas?

**Pete_UK** · 01-29-2013, 07:40 PM

I'd be tempted to use some helper columns on the Indirect sheet, one for each range, which identifies the records which match the various ranges, and then on the other sheet it would be relatively easy to pick those records up using an INDEX/MATCH combination. This would be better/easier than trying to devise some complex array formula for each range.

However, you've not put much sample data in your example workbook, so there is not much to work with.

Hope this helps.

Pete

**merlyn45** · 01-30-2013, 10:50 AM

Sorry about the sample worksheet, I should have put in more data. As for the helper columns..could you give me an example of what you mean?

**Pete_UK** · 01-31-2013, 05:14 AM

Take a look at the attachment in post #8 of this thread that I worked on last night:

http://www.excelforum.com/excel-gene...initially.html

There are examples of 4 helper columns on the Data sheet.

Hope this helps.

Pete