# 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  Register To Reply

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.  Register To Reply

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.  Register To Reply

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))),"")  Register To Reply

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.  Register To Reply