Excel - Vlookup formula for over 500k rows - how to speed this up?
1
I'm new to this forum and need your help.
I have a few tables on my report with over 500k rows in each and need to perform multiple vlookups. With the below vlookup formula it takes me forever to do so (I need to do it for the last few years, monthly).
My formula is as below:
=VLOOKUP(A:A,'Oct Corrected'!A:C,2,0)
How can I speed these calculations up? Can I use any different formula instead?
Thanks guys!
excel excel-formula vlookup
asked Nov 20 '18 at 18:47
TobiTobi
206
|
show 8 more comments
1
I'm new to this forum and need your help.
I have a few tables on my report with over 500k rows in each and need to perform multiple vlookups. With the below vlookup formula it takes me forever to do so (I need to do it for the last few years, monthly).
My formula is as below:
=VLOOKUP(A:A,'Oct Corrected'!A:C,2,0)
How can I speed these calculations up? Can I use any different formula instead?
Thanks guys!
excel excel-formula vlookup
asked Nov 20 '18 at 18:47
TobiTobi
206
4
Also if you have that much data, Excel is the wrong tool
– Scott Craner
Nov 20 '18 at 18:53
1
Maybe=INDEX('Oct Corrected'!$B:$B,MATCH(A2,'Oct Corrected'!$A:$A,0))?
– tigeravatar
Nov 20 '18 at 19:06
3
A database would be the correct tool. You would have yourOct Correctedas it's own table. You would have the list of values against which you are applying thisvlookup()as a second table. You would then join to get your results likeSELECT list_of_values.column1, oct_corrected.column2 FROM list_of_values INNER JOIN oct_corrected ON list_of_values.column1 = oct_corrected.column1;and it would fetch those results in the blink of an eye.
– JNevill
Nov 20 '18 at 19:18
1
Obviously getting that set up and learning SQL is a bit of a learning curve, but if you are going to routinely deal with larger data like this it's worth the pain of learning it. It's also a more marketable skill, so win-win ;)
– JNevill
Nov 20 '18 at 19:19
1
One solution is to use a doubleVLOOKUPas this speeds up the calculation dramatically. I am not going to post as an answer as it is already very well explained here : exceljet.net/formula/faster-vlookup-with-2-vlookups
– Peter K.
Nov 20 '18 at 21:38
|
show 8 more comments
1
1
1
I'm new to this forum and need your help.
I have a few tables on my report with over 500k rows in each and need to perform multiple vlookups. With the below vlookup formula it takes me forever to do so (I need to do it for the last few years, monthly).
My formula is as below:
=VLOOKUP(A:A,'Oct Corrected'!A:C,2,0)
How can I speed these calculations up? Can I use any different formula instead?
Thanks guys!
excel excel-formula vlookup
asked Nov 20 '18 at 18:47
TobiTobi
206
I'm new to this forum and need your help.
I have a few tables on my report with over 500k rows in each and need to perform multiple vlookups. With the below vlookup formula it takes me forever to do so (I need to do it for the last few years, monthly).
My formula is as below:
=VLOOKUP(A:A,'Oct Corrected'!A:C,2,0)
How can I speed these calculations up? Can I use any different formula instead?
Thanks guys!
excel excel-formula vlookup
excel excel-formula vlookup
asked Nov 20 '18 at 18:47
TobiTobi
206
asked Nov 20 '18 at 18:47
TobiTobi
206
edited Nov 20 '18 at 18:49
Tobi
asked Nov 20 '18 at 18:47
TobiTobi
206
asked Nov 20 '18 at 18:47
TobiTobi
206
asked Nov 20 '18 at 18:47
TobiTobi
206
206
4
Also if you have that much data, Excel is the wrong tool
– Scott Craner
Nov 20 '18 at 18:53
1
Maybe=INDEX('Oct Corrected'!$B:$B,MATCH(A2,'Oct Corrected'!$A:$A,0))?
– tigeravatar
Nov 20 '18 at 19:06
3
A database would be the correct tool. You would have yourOct Correctedas it's own table. You would have the list of values against which you are applying thisvlookup()as a second table. You would then join to get your results likeSELECT list_of_values.column1, oct_corrected.column2 FROM list_of_values INNER JOIN oct_corrected ON list_of_values.column1 = oct_corrected.column1;and it would fetch those results in the blink of an eye.
– JNevill
Nov 20 '18 at 19:18
1
Obviously getting that set up and learning SQL is a bit of a learning curve, but if you are going to routinely deal with larger data like this it's worth the pain of learning it. It's also a more marketable skill, so win-win ;)
– JNevill
Nov 20 '18 at 19:19
1
One solution is to use a doubleVLOOKUPas this speeds up the calculation dramatically. I am not going to post as an answer as it is already very well explained here : exceljet.net/formula/faster-vlookup-with-2-vlookups
– Peter K.
Nov 20 '18 at 21:38
|
show 8 more comments
4
Also if you have that much data, Excel is the wrong tool
– Scott Craner
Nov 20 '18 at 18:53
1
Maybe=INDEX('Oct Corrected'!$B:$B,MATCH(A2,'Oct Corrected'!$A:$A,0))?
– tigeravatar
Nov 20 '18 at 19:06
3
A database would be the correct tool. You would have yourOct Correctedas it's own table. You would have the list of values against which you are applying thisvlookup()as a second table. You would then join to get your results likeSELECT list_of_values.column1, oct_corrected.column2 FROM list_of_values INNER JOIN oct_corrected ON list_of_values.column1 = oct_corrected.column1;and it would fetch those results in the blink of an eye.
– JNevill
Nov 20 '18 at 19:18
1
Obviously getting that set up and learning SQL is a bit of a learning curve, but if you are going to routinely deal with larger data like this it's worth the pain of learning it. It's also a more marketable skill, so win-win ;)
– JNevill
Nov 20 '18 at 19:19
1
One solution is to use a doubleVLOOKUPas this speeds up the calculation dramatically. I am not going to post as an answer as it is already very well explained here : exceljet.net/formula/faster-vlookup-with-2-vlookups
– Peter K.
Nov 20 '18 at 21:38
4
4
Also if you have that much data, Excel is the wrong tool
– Scott Craner
Nov 20 '18 at 18:53
Also if you have that much data, Excel is the wrong tool
– Scott Craner
Nov 20 '18 at 18:53
1
1
Maybe
=INDEX('Oct Corrected'!$B:$B,MATCH(A2,'Oct Corrected'!$A:$A,0)) ?– tigeravatar
Nov 20 '18 at 19:06
Maybe
=INDEX('Oct Corrected'!$B:$B,MATCH(A2,'Oct Corrected'!$A:$A,0)) ?– tigeravatar
Nov 20 '18 at 19:06
3
3
A database would be the correct tool. You would have your
Oct Corrected as it's own table. You would have the list of values against which you are applying this vlookup() as a second table. You would then join to get your results like SELECT list_of_values.column1, oct_corrected.column2 FROM list_of_values INNER JOIN oct_corrected ON list_of_values.column1 = oct_corrected.column1; and it would fetch those results in the blink of an eye.– JNevill
Nov 20 '18 at 19:18
A database would be the correct tool. You would have your
Oct Corrected as it's own table. You would have the list of values against which you are applying this vlookup() as a second table. You would then join to get your results like SELECT list_of_values.column1, oct_corrected.column2 FROM list_of_values INNER JOIN oct_corrected ON list_of_values.column1 = oct_corrected.column1; and it would fetch those results in the blink of an eye.– JNevill
Nov 20 '18 at 19:18
1
1
Obviously getting that set up and learning SQL is a bit of a learning curve, but if you are going to routinely deal with larger data like this it's worth the pain of learning it. It's also a more marketable skill, so win-win ;)
– JNevill
Nov 20 '18 at 19:19
Obviously getting that set up and learning SQL is a bit of a learning curve, but if you are going to routinely deal with larger data like this it's worth the pain of learning it. It's also a more marketable skill, so win-win ;)
– JNevill
Nov 20 '18 at 19:19
1
1
One solution is to use a double
VLOOKUP as this speeds up the calculation dramatically. I am not going to post as an answer as it is already very well explained here : exceljet.net/formula/faster-vlookup-with-2-vlookups– Peter K.
Nov 20 '18 at 21:38
One solution is to use a double
VLOOKUP as this speeds up the calculation dramatically. I am not going to post as an answer as it is already very well explained here : exceljet.net/formula/faster-vlookup-with-2-vlookups– Peter K.
Nov 20 '18 at 21:38
|
show 8 more comments
1 Answer
1
active
oldest
votes
0
Try to use macro then add like this
Application.ScreenUpdating = False
Application.WorksheetFunction.VLookup(A:A,'Oct Corrected'!A:C,2,0)
Application.ScreenUpdating = True
answered Nov 21 '18 at 3:27
ReymondReymond
125
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53399578%2fexcel-vlookup-formula-for-over-500k-rows-how-to-speed-this-up%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
Try to use macro then add like this
Application.ScreenUpdating = False
Application.WorksheetFunction.VLookup(A:A,'Oct Corrected'!A:C,2,0)
Application.ScreenUpdating = True
answered Nov 21 '18 at 3:27
ReymondReymond
125
add a comment |
0
Try to use macro then add like this
Application.ScreenUpdating = False
Application.WorksheetFunction.VLookup(A:A,'Oct Corrected'!A:C,2,0)
Application.ScreenUpdating = True
answered Nov 21 '18 at 3:27
ReymondReymond
125
add a comment |
0
0
0
Try to use macro then add like this
Application.ScreenUpdating = False
Application.WorksheetFunction.VLookup(A:A,'Oct Corrected'!A:C,2,0)
Application.ScreenUpdating = True
answered Nov 21 '18 at 3:27
ReymondReymond
125
Try to use macro then add like this
Application.ScreenUpdating = False
Application.WorksheetFunction.VLookup(A:A,'Oct Corrected'!A:C,2,0)
Application.ScreenUpdating = True
answered Nov 21 '18 at 3:27
ReymondReymond
125
answered Nov 21 '18 at 3:27
ReymondReymond
125
answered Nov 21 '18 at 3:27
ReymondReymond
125
answered Nov 21 '18 at 3:27
ReymondReymond
125
125
add a comment |
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53399578%2fexcel-vlookup-formula-for-over-500k-rows-how-to-speed-this-up%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
4
Also if you have that much data, Excel is the wrong tool
– Scott Craner
Nov 20 '18 at 18:53
1
Maybe
=INDEX('Oct Corrected'!$B:$B,MATCH(A2,'Oct Corrected'!$A:$A,0))?– tigeravatar
Nov 20 '18 at 19:06
3
A database would be the correct tool. You would have your
Oct Correctedas it's own table. You would have the list of values against which you are applying thisvlookup()as a second table. You would then join to get your results likeSELECT list_of_values.column1, oct_corrected.column2 FROM list_of_values INNER JOIN oct_corrected ON list_of_values.column1 = oct_corrected.column1;and it would fetch those results in the blink of an eye.– JNevill
Nov 20 '18 at 19:18
1
Obviously getting that set up and learning SQL is a bit of a learning curve, but if you are going to routinely deal with larger data like this it's worth the pain of learning it. It's also a more marketable skill, so win-win ;)
– JNevill
Nov 20 '18 at 19:19
1
One solution is to use a double
VLOOKUPas this speeds up the calculation dramatically. I am not going to post as an answer as it is already very well explained here : exceljet.net/formula/faster-vlookup-with-2-vlookups– Peter K.
Nov 20 '18 at 21:38