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.

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

<tbody>
</tbody>

 

[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.