vba help - replace double vlookup with Dictionary

[Mallesh23]

Hi Team,

I am usisng double vlookup with iferror.
how to achieve same output using dictionary. first check first Table if not found check in Second Table.

First Table :=====> Column AB
Second Table :=====> Column DE

Expected Output :=====> Column H

Below is a Sample data.

Book1
	A	B	C	D	E	F	G	H
1	ID	Fruits		ID	Fruits		Expected Output
2	1	Apple		4	Orange		ID	Fruits
3	2	Banana		5	Mango		3	Kiwi
4	3	Kiwi		6	Pear		4	Orange
5	10	Cherry		7	Lemon		8	Not Found
6				10	Guava		7	Lemon
7							1	Apple
8							9	Not Found
9							10	Cherry
Sheet1

Cell Formulas
Range	Formula
H3:H8	H3	=IFERROR(VLOOKUP(G3,$A$1:$B$4,2,0),IFERROR(VLOOKUP(G3,$D$1:$E$5,2,0),"Not Found"))
H9	H9	=IFERROR(VLOOKUP(G9,$A$1:$B$5,2,0),IFERROR(VLOOKUP(G9,$D$1:$E$6,2,0),"Not Found"))

Thanks
mg

 

[Fluff]

How about something like

Sub Mallesh()
   Dim Cl As Range
   
   With CreateObject("scripting.dictionary")
      For Each Cl In Range("A2:A5")
         If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Range("D2:D6")
         If Not .Exists(Cl.Value) Then .Add Cl.Value, Cl.Offset(, 1).Value
      Next Cl
      For Each Cl In Range("G2:G9")
         If .Exists(Cl.Value) Then Cl.Offset(, 1).Value = .Item(Cl.Value)
      Next Cl
   End With
      
End Sub

 

[Mallesh23]

Hi Fluff,

Perfect ! thank you so much for your help. it worked. ?


Thanks
mg

 

[Fluff]

Glad to help & thanks for the feedback.