formula for multiple search and input to another cell from information column

[PuntingJawa]

I'm trying to shorten a formula to automate input to another cell. I have a list in column T3 through T35. What I am trying to do is if you input a number in a cell in E column and it matches one of the cells in T3-T35 it would return the value in S3 through S35 column into D column.
Example:
If I input a phrase in E2 it'll search T3 through T35. Say it finds that the match in T15 it'll input S15 into D2.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Location	Unique	Code	Part ID													Parts List
2																		Name	Code	Part ID
3																		L	164	s5530
4																		R	165	s5193
5																		E	118	s62
6																		D	166	s5211
7																		S	167	s6347
8																		ML	168	s5213
9																		MR	169	s5214
10																		MLR	170	s6282
11																		MLL	171	s6283
12																		Cin	172	s6284
13																		Cex	173	s6285
14																		GEX	174	s6286
15																		GIN	175	s6287
16																		MK	176	s6290
17																		CID	177	s6291
18																		DHL	279	s6726
19																		DHR	280	s6728
20																		H-01	694	s6740-01
21																		H-02	95	s6740-02
22																		H-03	696	s6740-03
23																		H-04	697	s6740-04
24																		H-05	698	s6740-05
25																		H-06	699	s6740-06
26																		H-08	701	s6740-08
27																		H-09	702	s6740-09
28																		H-10	703	s6740-10
29																		H-11	704	s6740-11
30																		H-12	705	s6740-12
31																		H-13	706	s6740-13
32																		H-14	707	s6740-14
33																		H-15	709	s6740-16
34																		H-16	804	s6740-17
35																		H-17	805	s6740-18
Sheet1

 

[etaf]

=FILTER(S3:S35,T3:T35=E2)

Book7
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1		Location	Unique	Code	Part ID													Parts List
2				118	s62			T3 through T35. What I am trying to do is if you input a number in a cell in E column and it matches one of the cells in T3-T35 it would return the value in S3 through S35 column into D column. Example: If I input a phrase in E2 it'll search T3 through T35. Say it finds that the match in T15 it'll input S15 into D2.										Name	Code	Part ID
3																		L	164	s5530
4																		R	165	s5193
5																		E	118	s62
6																		D	166	s5211
7																		S	167	s6347
8																		ML	168	s5213
9																		MR	169	s5214
10																		MLR	170	s6282
11																		MLL	171	s6283
12																		Cin	172	s6284
13																		Cex	173	s6285
14																		GEX	174	s6286
15																		GIN	175	s6287
16																		MK	176	s6290
17																		CID	177	s6291
18																		DHL	279	s6726
19																		DHR	280	s6728
20																		H-01	694	s6740-01
21																		H-02	95	s6740-02
22																		H-03	696	s6740-03
23																		H-04	697	s6740-04
24																		H-05	698	s6740-05
25																		H-06	699	s6740-06
26																		H-08	701	s6740-08
27																		H-09	702	s6740-09
28																		H-10	703	s6740-10
29																		H-11	704	s6740-11
30																		H-12	705	s6740-12
31																		H-13	706	s6740-13
32																		H-14	707	s6740-14
33																		H-15	709	s6740-16
34																		H-16	804	s6740-17
35																		H-17	805	s6740-18
Sheet1

Cell Formulas
Range	Formula
D2	D2	=FILTER(S3:S35,T3:T35=E2)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E													R	S	T
1		Location	Unique	Code	Part ID													Parts List
2				118	s62													Name	Code	Part ID
3																		L	164	s5530
4				175	s6287													R	165	s5193
5																		E	118	s62
6																		D	166	s5211
7																		S	167	s6347
8																		ML	168	s5213
9																		MR	169	s5214
10																		MLR	170	s6282
11																		MLL	171	s6283
12																		Cin	172	s6284
13																		Cex	173	s6285
14																		GEX	174	s6286
15																		GIN	175	s6287
16																		MK	176	s6290
17																		CID	177	s6291
18																		DHL	279	s6726
19																		DHR	280	s6728
20																		H-01	694	s6740-01
21																		H-02	95	s6740-02
22																		H-03	696	s6740-03
23																		H-04	697	s6740-04
24																		H-05	698	s6740-05
25																		H-06	699	s6740-06
26																		H-08	701	s6740-08
27																		H-09	702	s6740-09
28																		H-10	703	s6740-10
29																		H-11	704	s6740-11
30																		H-12	705	s6740-12
31																		H-13	706	s6740-13
32																		H-14	707	s6740-14
33																		H-15	709	s6740-16
34																		H-16	804	s6740-17
35																		H-17	805	s6740-18
Master

Cell Formulas
Range	Formula
D2:D19	D2	=IF(E2="","",IFNA(INDEX($S$3:$S$35,MATCH(E2,$T$3:$T$35,0)),""))

 

[etaf]

would you give some examples of

If I input a phrase in E2 it'll search T3 through T35. Say it finds that the match in T15 it'll input S15 into D2.

in case that different to

if you input a number in a cell in E column and it matches one of the cells in T3-T35 it would return the value in S3 through S35 column into D column.

 

[PuntingJawa]

would you give some examples of

in case that different to

I thought about the filter formula as well, but it just causes a spill error. I'm still new to learning =filter so I am learning how it reacts.
Fluff had the right idea with using an =IF formula. The one I was going to do was going to be massive as I would have the longest string "=IF" I have ever written.

How about

Fluff.xlsm
	A	B	C	D	E													R	S	T
1		Location	Unique	Code	Part ID													Parts List
2				118	s62													Name	Code	Part ID
3																		L	164	s5530
4				175	s6287													R	165	s5193
5																		E	118	s62
6																		D	166	s5211
7																		S	167	s6347
8																		ML	168	s5213
9																		MR	169	s5214
10																		MLR	170	s6282
11																		MLL	171	s6283
12																		Cin	172	s6284
13																		Cex	173	s6285
14																		GEX	174	s6286
15																		GIN	175	s6287
16																		MK	176	s6290
17																		CID	177	s6291
18																		DHL	279	s6726
19																		DHR	280	s6728
20																		H-01	694	s6740-01
21																		H-02	95	s6740-02
22																		H-03	696	s6740-03
23																		H-04	697	s6740-04
24																		H-05	698	s6740-05
25																		H-06	699	s6740-06
26																		H-08	701	s6740-08
27																		H-09	702	s6740-09
28																		H-10	703	s6740-10
29																		H-11	704	s6740-11
30																		H-12	705	s6740-12
31																		H-13	706	s6740-13
32																		H-14	707	s6740-14
33																		H-15	709	s6740-16
34																		H-16	804	s6740-17
35																		H-17	805	s6740-18
Master

Cell Formulas
Range	Formula
D2:D19	D2	=IF(E2="","",IFNA(INDEX($S$3:$S$35,MATCH(E2,$T$3:$T$35,0)),""))

Thank you so much! With my reply above, my formula would have been massive and hard to fix if I added more lines. I appreciate it!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[etaf]

I thought about the filter formula as well, but it just causes a spill error.

that usually means the cells that the filter() is trying to write into already has data in

so if you had
=filter(b2:b10 )
in say Z2
the the cells from Z3 to Z10 need to be empty - for the array formula to fill
so for
=filter(B2:D10 )
in Z2 , then
Z, AA, AB and AC and rows 2:10 would need to be clear

Book7
	D	E	F
1	Code	Part ID
2	Name	Code	Part ID
3	L	164	s5530
4	R	165	s5193
5	E	118	s62
6	D	166	s5211
7	S	167	s6347
8	ML	168	s5213
9	MR	169	s5214
10	MLR	170	s6282
Sheet1

Cell Formulas
Range	Formula
D2:F10	D2	=FILTER(R2:T10,U2:U10="")
Dynamic array formulas.