# Extract matches based on partial criteria in one column and exact match on another column

1. ## Extract matches based on partial criteria in one column and exact match on another column

Good day all,

I have this formula which works good to extract partial matches in range B1:B100
=INDEX(\$B\$2:\$B\$19,AGGREGATE(15,6,(ROW(\$B\$2:\$B\$19)-ROW(\$B\$2)+1)/ISNUMBER(SEARCH(\$B\$22,\$B\$2:\$B\$19)),ROW(A27)-ROW(\$A\$26)))

This formula works well in extracting partial criteria (ISNUMBER/SEARCH) defined on B22 from data on Column B.

However, I'd like to include other criterias from Column C and D. Please refer to attached worksheet example. My formula can only assist me to get results A26:D36. However, I'd like to get results in A38:D40.

Thanks

2. ## Re: Extract matches based on partial criteria in one column and exact match on another col

=IFERROR(INDEX(\$B\$2:\$B\$19,AGGREGATE(15,6,(ROW(\$B\$2:\$B\$19)-ROW(\$B\$2)+1)/(LEFT(\$B\$2:\$B\$19,4)=\$B\$22&"")/(\$C\$2:\$C\$19=\$B\$23)/(\$D\$2:\$D\$19=\$B\$24),ROWS(A\$26:A26))),"")

I change Search to Left, as Prefix should be on the left, for Search, 5219 could be anywhere.

3. ## Re: Extract matches based on partial criteria in one column and exact match on another col

Ver 2007 does not have Aggregate function.
ARRAY function in A26 then drag across upto column D till Empty cells are seen
``Please Login or Register  to view this content.``
How ARRAY formula is entered

Paste Formula in the cell.
Press F2
Hold Shift+ Ctrl Keys and hit Enter key.
Now the formula is surrounded by {} by excel.

4. ## Re: Extract matches based on partial criteria in one column and exact match on another col

Another B27 for 2007 normal enter

=IFERROR(INDEX(B:B,-1/(1/MOD(LARGE(INDEX((10^5-ROW(\$B\$2:\$B\$20))*(LEFT(\$B\$2:\$B\$20,4)=\$B\$22&"")*(\$C\$2:\$C\$20=\$B\$23)*(\$D\$2:\$D\$20=\$B\$24),),ROWS(B\$27:B27)),-10^5))),"")

5. ## Re: Extract matches based on partial criteria in one column and exact match on another col

Thanks everyone. @Bo_Ry formula on his last post worked like a charm without Array.

##### Users Browsing this Thread

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