# Problem using INDEX array extracting results from IF search )duplicate results search)

1. ## Problem using INDEX array extracting results from IF search )duplicate results search)

I am trying to create a routine to type in a surname and it will extract multiple rows of results where the name repeats. I have managed to identify the rows where the names appear, but when trying to index the results to extract all the completed cells on each row things are going wrong. I cant see any issues with my VB and am hoping it is something simple.

I have created a basic sample sheet where the data is in cells A5:E13, my input field is G4 and the results are B17 onwards.

2. ## Re: Problem using INDEX array extracting results from IF search )duplicate results search)

Notice that A17 returns the row in the worksheet itself, not the row of the table. You must subtract 4 from that value to get the correct table row.Index on the table with the calculated row and the desired column.

3. ## Re: Problem using INDEX array extracting results from IF search )duplicate results search)

You may try at B17 with CSE

=IFERROR(INDEX(A:A,SMALL(IF(\$A\$5:\$A\$13=\$G\$4,ROW(\$A\$5:\$A\$13)),ROW(1:1))),"")

or normal enter

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(\$A\$5:\$A\$13)/(\$A\$5:\$A\$13=\$G\$4),ROW(1:1))),"")

4. ## Re: Problem using INDEX array extracting results from IF search )duplicate results search)

Thanks for your help. I am almost finished now, I ave resolved most problems now except rows 2026 onward on sheet one which identify days between -7 and 30 days of the deadline. Is there a way to have then list in ascending order i.e. -7, -5-0,1, 14 etc. along with the columns identifying the names.

I have uploaded the latest sheet. If any protection is in place there is no password.

5. ## Re: Problem using INDEX array extracting results from IF search )duplicate results search)

Please try at B2026 Press Ctrl+Shift+Enter and copy down

=IFERROR(MATCH(SMALL(IF((\$B\$3:\$B\$2003<30)*(\$B\$3:\$B\$2003>-8),(\$B\$3:\$B\$2003)+ROW(\$B\$3:\$B\$2003)/10^6),ROWS(A\$1:A1)),(\$B\$3:\$B\$2003)+ROW(\$B\$3:\$B\$2003)/10^6,),"")

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1