Look up table for Left 3 and Mid 5,2 values and return Text in cell if found

[nahaku]

Hello, I have this table, it is list of empty locations. I would need to put under each Aisle 219,218... function where it will check the the list in column A, for first 3 number = Aisle that is for example in column C10, and on side are numbers of position in Aisle .30 for example. What are in columns D11 -> D32 and then if it was found it will return in column C11 "Empty"
every empty Cell in column C needs to check Table against The Aisle number what is on Top every Column and in same time on the position number next to it what is usually 01-48
I know that I can use for lookup =AND(Left(cell,3),(Mid(Cell,5,2)) but when I tried it it always returned Fault , probably because I do not know how to implement lookup in Array.

Copy of Free WH location Checker.xlsx
	A	B	C	D	E	F	G	H	I	J
9
10			219			219		218
11				44	43		43			44
12	Location			42	41		41			42
13	219.44.3.2			40	39		39			40
14	219.43.3.1			38	37		37			38
15	219.43.2.5			36	35		35			36
16	219.43.2.1			34	33		33			34
17	219.42.2.2			32	31		31			32
18	219.41.4.1			30	29		29			30
19	219.41.3.5			28	27		27			28
20	219.41.3.1			26	25		25			26
21	219.41.2.3			24	23		23			24
22	219.41.2.1			22	21		21			22
23	219.40.4.6			20	19		19			20
24	219.40.2.4			18	17		17			18
25	219.40.2.2			16	15		15			16
26	219.39.3.1			14	13		13			14
27	219.39.2.5			12	11		11			12
28	219.38.4.4			10	9		9			10
29	219.38.1.6			8	7		7			8
30	219.37.6.3			6	5		5			6
31	219.37.4.3			4	3		3			4
32	219.37.3.3			2	1		1			2
33	219.37.2.5
34	219.37.2.1
35	219.37.1.3
36	219.36.2.4
37	219.35.2.5
38	219.35.2.1
39	219.33.2.5
40	219.33.2.3
41	219.32.6.2
42	219.32.5.4
43	219.32.5.2
44	219.32.4.4
45	219.32.4.2
46	219.32.3.4
47	219.32.3.2
48	219.32.2.2
49	219.31.6.5
50	219.31.4.5
51	219.31.4.1
52	219.31.3.5
53	219.31.3.1
54	219.31.2.5
55	219.31.2.3
56	219.30.3.2
57	219.30.2.2
58	219.29.4.5
59	219.29.4.3
60	219.29.3.5
61	219.29.3.3
62	219.29.2.5
63	219.29.2.3
64	219.28.6.6
65	219.28.5.6
66	219.28.5.4
67	219.28.5.2
68	219.28.4.6
69	219.28.4.4
70	219.28.4.2
71	219.28.3.6
72	219.28.3.4
73	219.28.3.2
74	219.28.2.2
75	219.27.3.3
76	219.27.2.5
77	219.27.2.3
78	219.27.2.1
79	219.26.2.2
80	219.25.6.5
81	219.25.6.1
82	219.25.5.1
83	219.25.3.1
84	219.25.2.5
85	219.25.2.3
86	219.25.2.1
87	219.24.2.4
88	219.23.6.5
89	219.23.6.3
90	219.23.5.5
91	219.23.5.3
92	219.23.5.1
93	219.23.4.5
94	219.23.4.3
95	219.23.4.1
96	219.23.3.3
97	219.23.3.1
98	219.23.2.1
99	219.23.1.1
100	219.22.6.4
101	219.22.5.4
102	219.22.4.4
103	219.22.2.6
104	219.22.1.4
105	219.21.6.5
106	219.21.5.5
107	219.21.5.3
108	219.21.4.5
109	219.21.4.3
110	219.21.4.1
111	219.21.3.5
112	219.21.3.1
113	219.21.2.5
114	219.21.2.1
115	219.21.1.1
116	219.20.6.2
117	219.20.3.6
118	219.20.2.6
119	219.19.5.1
120	219.19.4.3
121	219.19.4.1
122	219.19.3.5
123	219.19.3.3
124	219.19.3.1
125	219.19.2.1
126	219.18.4.6
127	219.17.6.5
128	219.17.6.3
129	219.17.5.3
130	219.17.5.1
131	219.17.4.5
132	219.17.4.3
133	219.17.4.1
134	219.17.3.5
135	219.17.3.1
136	219.16.6.6
137	219.16.5.6
138	219.16.4.6
139	219.16.4.2
140	219.16.3.6
141	219.16.3.2
142	219.16.2.6
143	219.15.6.3
144	219.15.5.3
145	219.15.5.1
146	219.15.4.3
147	219.15.4.1
148	219.15.3.5
149	219.15.3.3
150	219.15.2.3
151	219.14.6.6
152	219.14.5.6
153	219.14.4.6
154	219.14.3.6
155	219.14.3.4
156	219.14.2.4
157	219.13.6.5
158	219.13.6.3
159	219.13.6.1
160	219.13.5.5
161	219.13.5.3
162	219.13.5.1
163	219.13.4.5
164	219.13.4.3
165	219.13.4.1
166	219.13.3.5
167	219.13.3.3
168	219.13.2.5
169	219.13.2.3
170	219.12.3.4
171	219.12.2.4
172	219.11.6.5
173	219.11.6.3
174	219.11.5.5
175	219.11.5.3
176	219.11.5.1
177	219.11.4.5
178	219.11.4.3
179	219.11.4.1
180	219.11.3.5
181	219.11.3.1
182	219.11.2.5
183	219.11.2.1
184	219.10.3.4
185	219.10.2.6
186	219.10.2.2
187	219.09.6.5
188	219.09.6.1
189	219.09.5.5
190	219.09.5.1
191	219.09.4.5
192	219.09.4.1
193	219.09.3.3
194	219.09.3.1
195	219.09.2.5
196	219.09.2.3
197	219.08.5.4
198	219.08.4.6
199	219.08.4.4
200	219.08.3.6
201	219.08.3.4
202	219.08.2.4
203	219.08.2.2
204	219.07.6.5
205	219.07.6.3
206	219.07.5.5
207	219.07.5.3
208	219.07.4.5
209	219.07.1.5
210	219.07.1.1
211	219.06.6.6
212	219.06.6.2
213	219.06.5.6
214	219.06.5.2
215	219.06.4.6
216	219.06.4.2
217	219.06.3.2
218	219.06.2.2
219	219.06.1.2
220	219.05.4.3
221	219.05.2.5
222	219.05.2.3
223	219.05.1.3
224	219.04.6.4
225	219.04.5.4
226	219.04.2.2
227	219.03.6.3
228	219.03.5.3
229	219.03.4.3
230	219.03.3.3
231	219.03.2.3
232	219.02.6.6
233	219.02.6.2
234	219.02.5.6
235	219.02.5.2
236	219.02.4.6
237	219.02.4.2
238	219.02.3.6
239	219.02.2.2
240	219.01.3.1
241	219.01.2.5
242	219.01.2.3
243	219.01.2.1
244	218.40.2.4
245	218.40.2.2
246	218.34.2.2
247	218.33.2.1
248	218.30.2.4
249	218.28.3.4
250	218.28.2.6
251	218.28.2.4
252	218.27.2.3
253	218.27.2.1
254	218.25.4.1
255	218.25.2.3
256	218.22.2.6
257	218.22.1.6
258	218.20.3.4
259	218.20.2.2
260	218.19.2.3
261	218.19.2.1
262	218.18.2.6
263	218.18.2.4
264	218.18.2.2
265	218.17.3.1
266	218.17.2.5
267	218.17.2.3
268	218.17.2.1
269	218.14.3.4
270	218.14.3.2
271	218.13.2.5
272	218.12.4.6
273	218.12.4.4
274	218.12.2.4
Sheet1

 

[jasonb75]

You might find it easier if you used a more consistent format.

Try this in C11 and fill down.

=IF(COUNTIF($A:$A,$C$10&"."&D11&".?.?"),"Found","NotFound")

 

[nahaku]

You might find it easier if you used a more consistent format.

Try this in C11 and fill down.

=IF(COUNTIF($A:$A,$C$10&"."&D11&".?.?"),"Found","NotFound")

Yes this one is working Thank you. :P
This just very small part of all data, it did not let me paste here more :D

 

[nahaku]

=IF(COUNTIF($A:$A,$C$10&"."&D11&".?.?"),"Found","NotFound")

I am not sure if you could help me with this problem too, I want to now look up that Table for value in Column D4, and it should list all values what beggins with D4 Value.
By the way, the Table should already have Unique Values, I used here Unique function because it usually worked for me, but now It cant show any results.

Copy of Free WH location Checker.xlsx
	A	B	C	D
4	Location		Location:	002.55
5	001.07.1.1			#CALC!
6	001.28.4.1
7	001.33.4.1
8	001.35.1.3
9	001.40.1.1
10	001.41.4.2
11	001.42.3.2
12	001.43.4.2
13	001.45.4.2
14	001.47.4.2
15	001.49.2.2
16	001.49.4.2
17	001.51.4.2
18	002.01.4.2
19	002.23.3.2
20	002.25.1.2
21	002.32.4.1
22	002.40.4.2
23	002.52.4.2
24	002.53.1.3
25	002.53.4.1
26	002.53.4.2
27	002.54.1.3
28	002.54.1.4
29	002.55.1.3
30	002.55.1.4
31	002.55.2.3
32	002.55.2.4
33	002.55.4.2
34	002.56.1.3
35
Sheet1

Cell Formulas
Range	Formula
D5	D5	=UNIQUE(FILTER(A5:A34,""&D4&"*"=A5:A34))

 

[Peter_SSs]

Like this?
Assuming D4 is a Text value

20 04 22.xlsm
	A	B	C	D
3
4	Location		Location:	002.55
5	001.07.1.1			002.55.1.3
6	001.28.4.1			002.55.1.4
7	001.33.4.1			002.55.2.3
8	001.35.1.3			002.55.2.4
9	001.40.1.1			002.55.4.2
10	001.41.4.2
11	001.42.3.2
12	001.43.4.2
13	001.45.4.2
14	001.47.4.2
15	001.49.2.2
16	001.49.4.2
17	001.51.4.2
18	002.01.4.2
19	002.23.3.2
20	002.25.1.2
21	002.32.4.1
22	002.40.4.2
23	002.52.4.2
24	002.53.1.3
25	002.53.4.1
26	002.53.4.2
27	002.54.1.3
28	002.54.1.4
29	002.55.1.3
30	002.55.1.4
31	002.55.2.3
32	002.55.2.4
33	002.55.4.2
34	002.56.1.3
35
FILTER

Cell Formulas
Range	Formula
D5:D9	D5	=FILTER(Table1[Location],LEFT(Table1[Location],LEN(D4))=D4,"")
Dynamic array formulas.

 

[jasonb75]

I don't have the UNIQUE function for testing, see if this works

=UNIQUE(FILTER(A5:A34,TEXT(D4,"000.00")&"*"=A5:A34))

If D4 contains 2.55 formatted as 000.00 then using ""&D4 will revert back to 2.55

 

[nahaku]

I don't have the UNIQUE function for testing, see if this works

=UNIQUE(FILTER(A5:A34,TEXT(D4,"000.00")&"*"=A5:A34))

If D4 contains 2.55 formatted as 000.00 then using ""&D4 will revert back to 2.55

For some reason it shows #Calc error,
I found on internet =INDEX($A$5:$A$34,SMALL(IF(""&$D$4&"*"=$A$5:$A$34,ROW($A$5:$A$34)-ROW($A$5)+1),ROW(4:4)))
but this will give me #Num error

 

[Peter_SSs]

Did you try the other suggestion (after adjusting for your table name)?

 

[nahaku]

Did you try the other suggestion (after adjusting for your table name)?

Yes this one worked, I am sorry, did not see your post as I scrolled down quickly and saw only post from jasonb75
However, Thank you Both.

 

[Peter_SSs]

Glad it worked for you.