searching with wildcards on variables instead of a constant string

Try:

=INDEX('Data Entry - Blast'!$A$8:$BR$65000,IF(ISNUMBER(MATCH(C1&"??"&E1&"*",'Data Entry - Blast'!$A$8:$A$65000,0)),ROW('Data Entry - Blast'!$A$8:$A$65000)-MIN(ROW('Data Entry - Blast'!$A$8:$A$65000))+1,""),COLUMN(A1))

Try this version:

=IFERROR(INDEX('Data Entry - Blast'!$A$8:$BR$65000,SMALL(IF(ISNUMBER(MATCH(C1&"??"&E1&"*",'Data Entry - Blast'!$A$8:$A$65000,0)),ROW('Data Entry - Blast'!$A$8:$A$65000)-MIN(ROW('Data Entry - Blast'!$A$8:$A$65000))+1),ROWS($A$1:$A1)),COLUMN(A1)),"")

You will need to confirme this formula holding the CTRL+SHIFT+ENTER keys down, not just ENTER so you get { } brackets around the formula... then copy down until you get blanks or past. This will extract consecutively matching rows.

Hi NBVC. The new code just picks up everything from my dataset in the exact order. It won't even take the match criteria into consideration. :/

I am not sure if you uploaded the correct workbook.. as it doesn't seem to follow the pattern of your initial request....

Try this.

Since you say you have a growing number of records, it is best to create a Dynamic Named Range in the Details sheet to accomodate and not to make formula remain as efficient as possible.

so in the ID Details sheet, go to Formulas tab, then Define Name, enter a name like: Details and then in the refers to field, enter formula:

='ID Details'!$A$1:INDEX('ID Details'!$F:$F,COUNTA('ID Details'!$A:$A))

Then back in the Search sheet enter formula in A15:

=IFERROR(INDEX(Details,SMALL(IF(ISNUMBER(SEARCH($B$3&"??"&$B$7&"*",INDEX(Details,0,1))),ROW(INDEX(Details,0,1))-MIN(ROW(INDEX(Details,0,1)))+1),ROWS($A$1:$A1)),COLUMN(A1)),"")

confirmed with CTRL+SHIFT+ENTER and copied across and down.

Your other workbook must be open to used named range from over there, here's how: http://www.contextures.com/xlDataVal05.html#Dynamic

... otherwise stick to fixed ranges, but minimize the number of rows included to max needed.

OMG Now it works!!!! Thank you so much.

From PM:

Originally Posted by I_need

Sorry to PM you but can you please help me modify your code below to pull out only 3 most recent rows that match the criteria? The date value is in Column "D" of the ID Details tab.

=IFERROR(INDEX('ID Details!$A$1:$BV$5000,SMALL(IF(ISNUMBER(SEARCH($B$7&"??"&$B$11&"*",INDEX('ID Details!$A$1:$BV$5000,0,1))),ROW(INDEX('ID Details!$A$1:$BV$5000,0,1))-MIN(ROW(INDEX('ID Details!$A$1:$BV$5000,0,1)))+1),ROWS($A$1:$A1)),COLUMN(A1)),"")

Thank you in advance.

If you change the SMALL to LARGE, you will get last match first and descending from there. Copy down only 3 rows to get last 3:

e.g.

=IFERROR(INDEX(Details,LARGE(IF(ISNUMBER(SEARCH($B$3&"??"&$B$7&"*",INDEX(Details,0,1))),ROW(INDEX(Details,0,1))-MIN(ROW(INDEX(Details,0,1)))+1),ROWS($A$1:$A1)),COLUMN(A1)),"")