Finding partial word using absolute formula

[Patcheen]

Hi All hope your all ok.

Im trying to find a partial word in a column if it partrial matches a word in cell "A1"

so if i put deo in A1 it should return all for modeos in the required column

this is my formula - IFERROR(@INDEX('All Vehicles Data'!D:D,SMALL(INDEX(('All Vehicles Data'!$D$20:$D$995,$A$1)*ROW('All Vehicles Data'!$D$20:$D$995),0),COUNTIF('All Vehicles Data'!$D$20:$D$995,"<>"&$A$1)+ROWS($20:20))),"")

is there any way of just editing the above formula?

 

[RoryA]

That formula doesn't make sense. Did you miss a bit?

If you're using 365, FILTER would seem like a better option.

 

[etaf]

as you have 365 - have you thought about using FILTER

you say <> NOT = to A1
anyway a
simplified example
=FILTER(D3:E10,ISNUMBER(SEARCH(A1,D3:D10)))

Book3
	A	B	C	D	E	F	G	H	I	J
1	deo
2
3				mondeo	1			mondeo	1
4				mondeo	2			mondeo	2
5				mondeo	3			mondeo	3
6				Audi	4			mondeo	5
7				mondeo	5			modeo	8
8				Audi	6
9				audi	7
10				modeo	8
11
Sheet1

Cell Formulas
Range	Formula
H3:I7	H3	=FILTER(D3:E10,ISNUMBER(SEARCH(A1,D3:D10)))
Dynamic array formulas.

 

[Patcheen]

as you have 365 - have you thought about using FILTER

you say <> NOT = to A1
anyway a
simplified example
=FILTER(D3:E10,ISNUMBER(SEARCH(A1,D3:D10)))

Book3
	A	B	C	D	E	F	G	H	I	J
1	deo
2
3				mondeo	1			mondeo	1
4				mondeo	2			mondeo	2
5				mondeo	3			mondeo	3
6				Audi	4			mondeo	5
7				mondeo	5			modeo	8
8				Audi	6
9				audi	7
10				modeo	8
11
Sheet1

Cell Formulas
Range	Formula
H3:I7	H3	=FILTER(D3:E10,ISNUMBER(SEARCH(A1,D3:D10)))
Dynamic array formulas.

nope never thoght of the filter option it works to do what i wanted a formula to do this does the trick. thank you.

 

[Patcheen]

Hi All hope your all ok.

Im trying to find a partial word in a column if it partrial matches a word in cell "A1"

so if i put deo in A1 it should return all for modeos in the required column

this is my formula - IFERROR(@INDEX('All Vehicles Data'!D:D,SMALL(INDEX(('All Vehicles Data'!$D$20:$D$995,$A$1)*ROW('All Vehicles Data'!$D$20:$D$995),0),COUNTIF('All Vehicles Data'!$D$20:$D$995,"<>"&$A$1)+ROWS($20:20))),"")

is there any way of just editing the above formula?

ya forgot the = at the begining sorry

 

[etaf]

nope never thoght of the filter option it works to do what i wanted a formula to do this does the trick. thank you.

you are welcome