VBA Excel Search every cell value from one column in another column and return output in desire columns

[paneliyadhruv]

Dear All,

Kindly help me to solve below mention problem.
In column A- master code from A2 to A
In column B- master code of fruit from B2 to B
In column C - list of fruits based on user entry

Procedure - Search column B name in column C, if name of that fruit present in column C then copy name in column E2 and master code of that fruit in column F. So on for searching of all cells of column B in column C and return values in E and F.

[TABLE="width: 639"]
<tbody>[TR]
[TD]Master Code[/TD]
[TD]Master List[/TD]
[TD]List based on User entry[/TD]
[TD][/TD]
[TD]Output required with code[/TD]
[TD]code[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apple[/TD]
[TD]grapefruit[/TD]
[TD][/TD]
[TD]apple[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]apricot[/TD]
[TD]lemon[/TD]
[TD][/TD]
[TD]apricot[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]avocado[/TD]
[TD]blueberry[/TD]
[TD][/TD]
[TD]blueberry[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]banana[/TD]
[TD]boysenberry[/TD]
[TD][/TD]
[TD]boysenberry[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]blackberry[/TD]
[TD][/TD]
[TD][/TD]
[TD]grapefruit[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]blackcurrant[/TD]
[TD]apple[/TD]
[TD][/TD]
[TD]lemon[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]blueberry[/TD]
[TD]apricot[/TD]
[TD][/TD]
[TD]melon[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]boysenberry[/TD]
[TD][/TD]
[TD][/TD]
[TD]nectarine[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]cherry[/TD]
[TD]melon[/TD]
[TD][/TD]
[TD]orange[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]coconut[/TD]
[TD]nectarine[/TD]
[TD][/TD]
[TD]pomegranate[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]fig[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD]quince[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]grape[/TD]
[TD]pomegranate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]grapefruit[/TD]
[TD]quince[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]kiwifruit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]lemon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]lime[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]lychee[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]mandarin[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]mango[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]melon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]nectarine[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]orange[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]papaya[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]passion fruit[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]peach[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]pear[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]pineapple[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]plum[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]pomegranate[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]quince[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]raspberry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]strawberry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]watermelon[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[NimishK]

Dhruv
You need to use VLOOKUP Formula in column E and in Column F

 

[paneliyadhruv]

I need macro. However, I have tried formula but taking more time when data is more.

 

[Fluff]

How about

Sub paneliyadhruv()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      .CompareMode = 1
      For Each Cl In Range("C2", Range("C" & Rows.Count).End(xlUp))
         If Not Cl.Value = "" Then .item(Cl.Value) = Empty
      Next Cl
      For Each Cl In Range("B2", Range("B" & Rows.Count).End(xlUp))
         If .Exists(Cl.Value) Then .item(Cl.Value) = Cl.Offset(, -1).Value
      Next Cl
      Range("E2").Resize(.Count, 2).Value = Application.Transpose(Array(.Keys, .Items))
   End With
End Sub

 

[Fluff]

Cross posted https://chandoo.org/forum/threads/s...mn-and-return-output-in-desire-columns.40936/

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[paneliyadhruv]

Dear all,
I am extremely sorry for not posting cross link. I will take care in future.