Search entire column data in a another column

PANIGGR

New Member
Joined
Sep 4, 2015
Messages
15
I have column A with random data alpha and alpha numeric. Column C with specific orderly data. I need to search value of column C exists in any section of column A. If yes then it should highlight in column B and update the the value.

Aptus Utilitie A7MWUO5V
A7MWUO5V
A3IPM3117C
snd vnvnnnkn
A39SYRN

<colgroup><col width="83"></colgroup><tbody>
</tbody>
gmsnn A1U040ZX 35454
A1U040ZX
A2H7QHYJ3

<colgroup><col width="83"></colgroup><tbody>
</tbody>
A1U040ZX7987ae0090
A1U040ZX
A7MWUO5V

<colgroup><col width="83"></colgroup><tbody>
</tbody>
wgags80980
A2Z9KP

<colgroup><col width="83"></colgroup><tbody>
</tbody>
aga9-098-0 A39SYRN
A39SYRN
A1U040ZX

<colgroup><col width="83"></colgroup><tbody>
</tbody>
ggrg0= A1U040ZX 098233
A1U040ZX
A1DN5HDW

<colgroup><col width="83"></colgroup><tbody>
</tbody>

<tbody>
</tbody>

Regards,
Radhakant
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).
will there be any blanks in column c?
and what about column a?
 
Last edited:
Upvote 0
With a UDF
Code:
Function PANIGGR(AStr As String, ColC As Range) As String
   Dim Cl As Range
   
   For Each Cl In ColC
      If InStr(1, AStr, Cl, vbTextCompare) > 0 Then
         PANIGGR = Cl.Value
         Exit Function
      End If
   Next Cl
   PANIGGR = ""
End Function


Book1
ABC
1
2Aptus Utilitie A7MWUO5VA7MWUO5VA3IPM3117C
3snd vnvnnnknA39SYRN
4gmsnn A1U040ZX 35454A1U040ZXA2H7QHYJ3
5A1U040ZX7987ae0090A1U040ZXA7MWUO5V
6wgags80980A2Z9KP
7aga9-098-0 A39SYRNA39SYRNA1U040ZX
8ggrg0= A1U040ZX 098233A1U040ZXA1DN5HDW
Merged
Cell Formulas
RangeFormula
B2=PANIGGR(A2,$C$2:$C$8)
 
Upvote 0
With a UDF
Code:
Function PANIGGR(AStr As String, ColC As Range) As String
   Dim Cl As Range
   
   For Each Cl In ColC
      If InStr(1, AStr, Cl, vbTextCompare) > 0 Then
         PANIGGR = Cl.Value
         Exit Function
      End If
   Next Cl
   PANIGGR = ""
End Function

ABC
1
2Aptus Utilitie A7MWUO5VA7MWUO5VA3IPM3117C
3snd vnvnnnknA39SYRN
4gmsnn A1U040ZX 35454A1U040ZXA2H7QHYJ3
5A1U040ZX7987ae0090A1U040ZXA7MWUO5V
6wgags80980A2Z9KP
7aga9-098-0 A39SYRNA39SYRNA1U040ZX
8ggrg0= A1U040ZX 098233A1U040ZXA1DN5HDW

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Merged

Worksheet Formulas
CellFormula
B2=PANIGGR(A2,$C$2:$C$8)

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>

After adding the vba, when i used the formula, i am getting output as #NAME ?
 
Upvote 0
Did you add the function to the workbook with the data, or to your personal.xlsb workbook?
 
Upvote 0
Where did you put the VBA code?
It needs to go in a standard module, in the same workbook as the data.
 
Upvote 0
Where did you put the VBA code?
It needs to go in a standard module, in the same workbook as the data.

Yes, i have used it in the new module and then used the formula =PANIGGR(A1,$C$1:$C$7) in column B.

Function PANIGGR(AStr As String, ColC As Range) As String
Dim Cl As Range

For Each Cl In ColC
If InStr(1, AStr, Cl, vbTextCompare) > 0 Then
PANIGGR = Cl.Value
Exit Function
End If
Next Cl
PANIGGR = ""
End Function
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top