Find the same value of a cell in a range of cells

[bluefeather8989]

for each cell that has a value in range M4:M9 find that same value in range AE2:AQ65 then add the name from L3 to the empty cell next to the number. I need sheets reference, Sheets ("DR").

Example: L3 value is John Smith. M4 value is 212 marco will find 212 in range AE2:AQ65 witch is cell AG13 input the name John Smith in cell AH 13.

 

[awoohaw]

Okay, i cannot post more than 3000 cells for you. But here is a non macro solution:
you'll have to adjust columns as necessary:

mr excel questions 24.xlsm
											K	L	M	N	O	P	Q	R	S	T	U
1
2														101		201		301		401
3												John Smith		102		202		302		402
4											1		212	103		203		303		403
5											2			104		204		304		404
6											3		313	105		205		305		405
7											4		213	106		206		306		406
8											5		903	107		207		307		407
9											6			108		208		308		408
10														109		209		309		409
11														110		210		310		410
12														111		211		311		411
13														112		212	John Smith	312		412
14														113		213	John Smith	313	John Smith	413
15														114		214		314		414
16														115		215		315		415
17														116		216		316		416
18														117		217		317		417
19														118		218		318		418
20														119		219		319		419
21														120		220		320		420
22														121		221		321		421
23														122		222		322		422
24														123		223		323		423
25														124		224		324		424
26														125		225		325		425
27														126		226		326		426
28														127		227		327		427
29														128		228		328		428
30														129		229		329		429
31														130		230		330		430
32														131		231		331		431
33														132		232		332		432
34														133		233		333		433
35														134		234		334		434
36														135		235		335		435
37														136		236		336		436
38														137		237		337		437
39														138		238		338		438
40														139		239		339		439
41														140		240		340		440
42														141		241		341		441
43														142		242		342		442
44														143		243		343		443
45														144		244		344		444
46														145		245		345		445
47														146		246		346		446
48														147		247		347		447
49														148		248		348		448
50														149		249		349		449
51														150		250		350		450
52														151		251		351		451
53														152		252		352		452
54														153		253		353		453
55														154		254		354		454
56														155		255		355		455
57														156		256		356		456
58														157		257		357		457
59														158		258		358		458
60														159		259		359		459
61														160		260		360		460
62														161		261		361		461
63														162		262		362		462
64														163		263		363		463
65														164		264		364		464
66														165		265		365		465
67														166		266		366		466
68														167		267		367		467
69														168		268		368		468
70														169		269		369		469
71														170		270		370		470
72														171		271		371		471
73														172		272		372		472
74														173		273		373		473
75														174		274		374		474
76														175		275		375		475
77														176		276		376		476
78														177		277		377		477
79														178		278		378		478
80														179		279		379		479
81														180		280		380		480
82														181		281		381		481
83														182		282		382		482
84														183		283		383		483
85														184		284		384		484
86														185		285		385		485
87														186		286		386		486
88														187		287		387		487
89														188		288		388		488
90														189		289		389		489
91														190		290		390		490
92														191		291		391		491
93														192		292		392		492
94														193		293		393		493
95														194		294		394		494
96														195		295		395		495
97														196		296		396		496
98														197		297		397		497
99														198		298		398		498
100														199		299		399		499
101														200		300		400		500
jpalmer20

Cell Formulas
Range	Formula
K4:K9	K4	=SEQUENCE(6,1,1,1)
O2:O101,U2:U101,S2:S101,Q2:Q101	O2	=IF(ISNUMBER(MATCH(N2,$M$4:$M$9,0)),$L$3,"")
Dynamic array formulas.

 

[Trebor76]

Hi bluefeather8989,

Here is a possible macro solution:

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim rngCell As Range, rngFind As Range
    
    Application.ScreenUpdating = False
    
    Set ws = ThisWorkbook.Sheets("Sheet1") '<- Sheet name containing the data. Change to suit.
    
    For Each rngCell In ws.Range("M4:M9")
        If Len(rngCell.Value) > 0 Then
            Set rngFind = ws.Range("AE2:AQ65").Find(What:=CDbl(rngCell.Value), LookIn:=xlValues)
            If Not rngFind Is Nothing Then
                rngFind.Offset(0, 1).Value = ws.Range("L3")
                Set rngFind = Nothing
            End If
        End If
    Next rngCell
    
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

 

[bluefeather8989]

Hi bluefeather8989,

Here is a possible macro solution:

Option Explicit
Sub Macro1()

    Dim ws As Worksheet
    Dim rngCell As Range, rngFind As Range
   
    Application.ScreenUpdating = False
   
    Set ws = ThisWorkbook.Sheets("Sheet1") '<- Sheet name containing the data. Change to suit.
   
    For Each rngCell In ws.Range("M4:M9")
        If Len(rngCell.Value) > 0 Then
            Set rngFind = ws.Range("AE2:AQ65").Find(What:=CDbl(rngCell.Value), LookIn:=xlValues)
            If Not rngFind Is Nothing Then
                rngFind.Offset(0, 1).Value = ws.Range("L3")
                Set rngFind = Nothing
            End If
        End If
    Next rngCell
   
    Application.ScreenUpdating = True

End Sub

Regards,

Robert

This works but only for the last number in the range (903)

 

[Trebor76]

This works but only for the last number in the range (903)

That's odd as it works for me

Are you sure the numbers to be searched are in the range M4:M9? Are the cells merged?

 

[bluefeather8989]

That's odd as it works for me

Are you sure the numbers to be searched are in the range M4:M9? Are the cells merged?

I am so stupid. I forgot to format the cells Tex color to white. It was set to default black. The names where there I just couldn't see it. Thanks for the help. LMAO

 

[Trebor76]

Thanks for letting us know and glad you got it sorted