Filter that uses search text from a range or column

[mejohn]

Hello everyone,

I'm trying to extract data from sheet1 into sheet2. One of the columns in sheet1 contains a personal identifier along with other text.

On sheet2 I use a filter function to extract data from sheet 1 by referencing cells in sheet2 column (i) that lists the personal identifiers I'm interested in.

=FILTER(INDEX(Sheet1!A:N,SEQUENCE(ROWS(Sheet1!A:N)),{2,3,4,6,9,11}),ISNUMBER(SEARCH(I2,Sheet1!K:K))+ISNUMBER(SEARCH(I3,Sheet1!K:K))+ISNUMBER(SEARCH(I4,Sheet1!K:K)),"")

The formula above works well, but for every new personal identifier I want to match, I have to add to the formula (+ISNUMBER(SEARCH(I?,Sheet1!K:K))). This formula also breaks if there're any empty cells referenced in column (i) of sheet2.

Is there a way to match the personal identifiers listed on sheet2 column (i) by referencing the entire column I:I, while using the search function? +ISNUMBER(SEARCH(I:I,Sheet1!K:K))

Thank you so much in advance for taking the time.

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How many rows of data do you actually have on sheet1?

 

[mejohn]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How many rows of data do you actually have on sheet1?

Hi Fluff,
I updated my profile. Thanks for the suggestion.

Sheet1 has two thousand rows and 14 columns.
Using the formula above, I'm only porting over columns {2,3,4,6,9,11} from sheet1 to sheet2.

Thank you

 

[Fluff]

Ok, how about

=LET(rng,TRANSPOSE(I2:I5),FILTER(INDEX(Sheet1!A2:N5000,SEQUENCE(ROWS(Sheet1!A2:N5000)),{2,3,4,6,9,11}),MMULT((rng<>"")*(ISNUMBER(SEARCH(rng,Sheet1!K2:K5000))),SEQUENCE(COLUMNS(rng)))))

 

[mejohn]

Ok, how about

=LET(rng,TRANSPOSE(I2:I5),FILTER(INDEX(Sheet1!A2:N5000,SEQUENCE(ROWS(Sheet1!A2:N5000)),{2,3,4,6,9,11}),MMULT((rng<>"")*(ISNUMBER(SEARCH(rng,Sheet1!K2:K5000))),SEQUENCE(COLUMNS(rng)))))

Fluff, this worked perfectly!

Thank you so much!!!

 

[Fluff]

You're welcome & thanks for the feedback.