vba Dictionary - replace vlookup for speed

Mallesh23

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

I need dictionary help to replace vlookup. as dictionary is faster.
I have dummy data Range("A1:G43") and Expected output is in Range J onward.

Column J (Player Name) is looking up value, trying to learn dictionary with this example. Thanks in advance for your help.

In my Actual data I have 2000 lookup value in Range of ("A1:Z30000") so looking for dictioanary help.


Below is table with expected output.
Book3
ABCDEFGHIJKLMN
1Sr NoPlayer NamePeriodTeamTest CenturyODI CenturyTotalPlayer NameTeamTest CenturyODI CenturyTotal
21Sachin Tendulkar1989–2013 India5149100Sachin Tendulkar India5149`
32Ricky Ponting1995–2012 Australia413071Ricky Ponting Australia413071
43Virat Kohli2008–2020 India274370Virat Kohli India274370
54Kumar Sangakkara2000–2015 Sri Lanka382563Brian Lara West Indies341953
65Jacques Kallis1995–2014 South Africa451762Rahul Dravid India361248
76Hashim Amla2004–2019 South Africa282755AB de Villiers South Africa222547
87Mahela Jayawardene1997–2015 Sri Lanka341953David Warner Australia241842
98Brian Lara1990–2007 West Indies341953Sanath Jayasuriya Sri Lanka142842
109Rahul Dravid1996–2012 India361248Chris Gayle West Indies25227
1110AB de Villiers2004–2018 South Africa222547Gary Kirsten South Africa212647
1211David Warner2009–2020 Australia241842Adam Gilchrist Australia172643
1312Sanath Jayasuriya1989–2011 Sri Lanka142842Joe Root England162541
1413Chris Gayle1989–2011 West Indies25227Kevin Pietersen England231639
1514Shivnarine Chanderpaul1989–2011 West Indies301141Saeed Anwar Pakistan201939
1615Ross Taylor1989–2011New Zealand707Allan Border Australia32326
1716Matthew Hayden1993–2009 Australia301040Don Bradman Australia292756
1817Gary Kirsten1993–2004 South Africa212647
1918Kane Williamson1993–2004 New Zealand133043
2019Adam Gilchrist1996–2008 Australia172643
2120Joe Root2004–2014 England162541
2221Kevin Pietersen2004–2014 England231639
2322Javed Miandad1975–1996 Pakistan231538
2423Aravinda de Silva1975–1996 Sri Lanka112435
2524Saeed Anwar1975–1996 Pakistan201939
2625Gordon Greenidge1974–1991 West Indies193049
2726Allan Border1974–1991 Australia32326
2827Don Bradman1928–1948 Australia292756
2928Mohammad Azharuddin1928–1948 India71522
3029Graham Gooch1975–1995 England201838
3130Greg Chappell1970–1984 Australia242852
3231Marvan Atapattu1970–1984 Sri Lanka112738
3332Nathan Astle1970–1984 New Zealand162036
3433Andrew Strauss1970–1984 England62531
3534Garfield Sobers1970–1984 West Indies262955
3635David Boon1970–1984 Australia53035
3736Marcus Trescothick1970–1984 England121729
3837Ian Bell1970–1984 England42529
3938David Gower1970–1984 England182240
4039Shikhar Dhawan2010–2020India72229
4140Geoffrey Boycott1964–1982 England222244
4241Justin Langer1964–1982 Australia232144
4342V. V. S. Laxman2000-2014 India62329
Sheet1
Cell Formulas
RangeFormula
K2K2=VLOOKUP($J2,$B$1:$G$43,3,0)
L2:L17L2=VLOOKUP($J2,$B$1:$G$43,4,0)
M2:M17M2=VLOOKUP($J2,$B$1:$G$43,5,0)
K3:K17K3=VLOOKUP(J3,$B$1:$G$43,3,0)
N3:N17N3=VLOOKUP($J3,$B$1:$G$43,6,0)
 
Yes that will work with 250k rows, although it might take a few seconds.
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Fluff,

I used vlookup on 1 lakshs Row ,
using dictionary approach as suggested , awsome speed. ? ?


Thanks
mg
 
Upvote 0
Glad to help & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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