vba help - replace double vlookup with Dictionary

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
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
ABCDEFGH
1IDFruitsIDFruitsExpected Output
21Apple4OrangeIDFruits
32Banana5Mango3Kiwi
43Kiwi6Pear4Orange
510Cherry7Lemon8Not Found
610Guava7Lemon
71Apple
89Not Found
910Cherry
Sheet1
Cell Formulas
RangeFormula
H3:H8H3=IFERROR(VLOOKUP(G3,$A$1:$B$4,2,0),IFERROR(VLOOKUP(G3,$D$1:$E$5,2,0),"Not Found"))
H9H9=IFERROR(VLOOKUP(G9,$A$1:$B$5,2,0),IFERROR(VLOOKUP(G9,$D$1:$E$6,2,0),"Not Found"))



Thanks
mg
 
Last edited:

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
How about something like
VBA Code:
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

Well-known Member
Joined
Feb 4, 2009
Messages
874
Office Version
  1. 2010
Platform
  1. Windows
Hi Fluff,

Perfect ! thank you so much for your help. it worked. 🕺 (y)


Thanks
mg
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
57,030
Office Version
  1. 365
Platform
  1. Windows
Glad to help & thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,424
Messages
5,642,037
Members
417,251
Latest member
Dordrecht

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
Top