Returning cell contents if contains partial text

[blittenb]

Hey all,

I am struggling to return cell contents if contains partial text "JS". I have it partially working with the filter function however it isn't ideal.

I want to be able to search for cells containing "JS" amongst sheets MDF, IDF-1, IDF-2, IDF-3, and IDF-4. The range of cells is the same on each sheets, I5:I52,P5:P52,W5:W52,AD5:AD52 and return every cells text.

 

[Fluff]

Hi & welcome to MrExcel.
If those sheets are consecutive, try

=LET(x,TOCOL(CHOOSECOLS(VSTACK('MDF:IDF-4'!I5:AD52),1,8,15,22)),FILTER(x,ISNUMBER(SEARCH("Js",x))))

 

[blittenb]

Hi & welcome to MrExcel.
If those sheets are consecutive, try

=LET(x,TOCOL(CHOOSECOLS(VSTACK('MDF:IDF-4'!I5:AD52),1,8,15,22)),FILTER(x,ISNUMBER(SEARCH("Js",x))))

Miracle worker, much appreciated.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[blittenb]

You're welcome & thanks for the feedback.

Any idea how to force sorting based on columns rather than the row they reside in ?

Nordstrom - LR Survey - Template.xlsx
	P	Q	R	S	T	U	V	W
41			12
42			11
43	JSxxx-IDF1-2-C:0		10
44			9					JSxxx-IDF1-3-X:0
45	JSxxx-IDF1-2-C:1		8
46			7					JSxxx-IDF1-3-X:1
47			6
IDF-1

Cell Formulas
Range	Formula
P43	P43	="JS"&B3&"-"&B5&"-"&K1&"-"&"C:0"
W44	W44	="JS"&B3&"-"&B5&"-"&R1&"-"&"X:0"
P45	P45	="JS"&B3&"-"&B5&"-"&K1&"-"&"C:1"
W46	W46	="JS"&B3&"-"&B5&"-"&R1&"-"&"X:1"

 

[blittenb]

So the IDF-1-2-C:0 and C:1 are sequential then C:0 and X:1 are sequential:

Nordstrom - LR Survey - Template.xlsx
	D
2	Hostnames
3	JSxxx-IDF1-2-C:0
4	JSxxx-IDF1-3-X:0
5	JSxxx-IDF1-2-C:1
6	JSxxx-IDF1-3-X:1
7
8
9
10
11
Ordering BOM

Cell Formulas
Range	Formula
D3:D6	D3	=LET(x,TOCOL(CHOOSECOLS(VSTACK('MDF:IDF-4'!I5:AD52),1,8,15,22)),FILTER(x,ISNUMBER(SEARCH("JS",x))))
Dynamic array formulas.

 

[blittenb]

So the IDF-1-2-C:0 and C:1 are sequential then C:0 and X:1 are sequential:

Nordstrom - LR Survey - Template.xlsx
	D
2	Hostnames
3	JSxxx-IDF1-2-C:0
4	JSxxx-IDF1-3-X:0
5	JSxxx-IDF1-2-C:1
6	JSxxx-IDF1-3-X:1
7
8
9
10
11
Ordering BOM

Cell Formulas
Range	Formula
D3:D6	D3	=LET(x,TOCOL(CHOOSECOLS(VSTACK('MDF:IDF-4'!I5:AD52),1,8,15,22)),FILTER(x,ISNUMBER(SEARCH("JS",x))))
Dynamic array formulas.

Correction: So the IDF-1-2-C:0 and C:1 are sequential then X:0 and X:1 are sequential:

 

[Fluff]

As this is a totally different question, it needs a new thread. Thanks