How To Return A Row Of Values If A Value Is Found Anywhere In That Row

[excelbytes]

In the example in the image attached, I want to search anywhere in columns D1:J106 for the values listed in column L and if that value is found anywhere in D1:J106, list the values starting in column M. The example I am showing that's highlighted if value 240 in L13. It is found in cell E10, so the values from D10:J10 are listed in M13:S13.

 

[Eric W]

Try:

Book1
	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Column 1	Column 2	Column 3	Column 4	Column 5	Column 6	Column 7		Value
2	557	314							201
3	324	904							203
4	220	740							205
5	223	717							208
6	224	847							210
7	226	382	548	519					212	332	646	917	212
8	234	330							214
9	236	672	778	604	250				215	267	445	215
10	227	240	301						217	447	217
11	249	705							224	224	847
12	267	445	215						234	234	330
13	272	570							240	227	240	301
14	279	916							248
15	281	346	832	713					256
16	289	365	905						267	267	445	215
17	321	407							270
18	323	213							281	281	346	832	713
19	326	937							301	227	240	301
20	331	630							303
21	332	646	917	212					304
22	339	781							305
23	341	510							310	424	310
24	343	613							312
25	347	929	718						314	557	314
26	351	978							315
27	364	570							321	321	407
28	367	581	418						323	323	213
29	368	587	825	780	403				330	234	330
30	380	614							331	331	630
31	385	801							336
32	424	310							347	347	929	718
33	430	903							360
34	431	204							385	385	801
35	437	647	416						402
36	438	514							404
37	442	760							405
38	443	667	410						407	321	407
39	447	217							408
40	448	850							410	443	667	410
41	458	541							412
Sheet1

Cell Formulas
Range	Formula
M2:M6,M8,M14:M15,M17,M20:M22,M24,M26,M31,M33,M35:M37,M39,M41,M40:S40,M38:S38,M34:S34,M32:S32,M27:S30,M25:S25,M23:S23,M18:S19,M16:S16,M9:S13,M7:S7	M2	=LET(t,$D$2:$J$41,m,MAX(IF(L2=t,ROW(t)-ROW(INDEX(t,1,1))+1)),IF(m=0,"",INDEX(t,m,0)&""))

 

[Tetra201]

Another approach:

=LET(a,$D$2:$J$41,IFERROR(INDEX(a,MATCH(1,SIGN(MMULT(--(L2=a),{1;1;1;1;1;1;1})),0),0)&"",""))

Note: this formula returns the row with the first occurrence of, say, 570, whereas formula from Post #2 returns the row with the last occurrence.

 

[Fluff]

Another option, that will return the 1st occurrence.

=LET(a,TAKE(FILTER($D$2:$J$100,BYROW($D$2:$J$100,LAMBDA(br,COUNTIFS(br,L2))),""),1),IF(a="","",a))

 

[Eric W]

I managed to shorten my formula a bit:

=LET(t,$D$2:$J$41,m,MAX(IF(L2=t,ROW(t)-MIN(ROW(t))+1)),IF(m,INDEX(t,m,0)&"",""))

Also, I suspect these are North American area codes. Each row has the codes for a specific state/province/region. If so, there will only be 1 occurrence of each number.