vlookup - excel slow

[Human2014]

Hello everybody

is there a way to make an excel file quicker?

I took this file over from another person and it has many vlookups.
My guess is vlookup could be the reason for this.

Example of formulas:
=VLOOKUP($C179985,'Mapping for Salary Tracker'!$A$2:$A$48,1,FALSE)
=IFERROR((VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$L:$M,2,FALSE)),"-")
=IFERROR((VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$O:$P,2,FALSE)),"-")
=IFERROR((VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$S:$T,2,FALSE)),"-")
=VLOOKUP(VALUE(A179985),Sheet2!$A:$C,3,0)

thanks for the feedback

[alansidman]

Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).


CROSSPOSTED: http://www.mrexcel.com/forum/excel-q...xcel-slow.html

[Glenn Kennedy]

The first obvious suggestion is to remove the references to whole columns (e.g. $L:$M). Can you replace these with realistic ranges?

[Human2014]

Well, my range is L and M.
I don't think I can replace it.

The first obvious suggestion is to remove the references to whole columns (e.g. $L:$M). Can you replace these with realistic ranges?

[Glenn Kennedy]

Do your data REALLY go down to row 1048576???? If not, you are wasting a lot of time looking at empty cells.

[MrShorty]

1) Another thing that slows lookups down is the fourth argument is FALSE -- which means a linear search. Linear searches are much slower (especially on large data sets) than binary searches. If you can sort the data in the lookup table, then you can use TRUE for the fourth argument of VLOOKUP(), and use a binary search.

2) Another thing that slows spreadsheets down is duplicated effort. I notice that your three middle lookups (that are probably consuming most of the computation time) are searching for the same value in three "adjacent" lookup tables. If you can put some effort into "mapping for salary tracker", you may be able to turn three slow lookups into one lookup (via match() function see discussion and example here: http://www.excelforum.com/excel-form...ows-excel.html see posts 6 and 13), which should speed up your file considerably. (especially if the cleanup of mapping for salary tracker need be performed only once).

[Human2014]

I updated my formulas according to all of the comments.

This is what it looks like now:
=VLOOKUP($C2,'Mapping for Salary Tracker'!$A$2:$A$48,1,FALSE)
=IFERROR((VLOOKUP(S2,'Mapping for Salary Tracker'!$L$2:$M$141,2,FALSE)),"-")
=IFERROR((VLOOKUP(S2,'Mapping for Salary Tracker'!$O$2:$P$46,2,FALSE)),"-")
=IFERROR((VLOOKUP(S2,'Mapping for Salary Tracker'!$S$2:$T$20,2,FALSE)),"-")
=VLOOKUP(VALUE(A2),Sheet2!$A:$C,3,0)

Any other improvements ? or ideas ?

Thanks guys

1) Another thing that slows lookups down is the fourth argument is FALSE -- which means a linear search. Linear searches are much slower (especially on large data sets) than binary searches. If you can sort the data in the lookup table, then you can use TRUE for the fourth argument of VLOOKUP(), and use a binary search.

2) Another thing that slows spreadsheets down is duplicated effort. I notice that your three middle lookups (that are probably consuming most of the computation time) are searching for the same value in three "adjacent" lookup tables. If you can put some effort into "mapping for salary tracker", you may be able to turn three slow lookups into one lookup (via match() function see discussion and example here: http://www.excelforum.com/excel-form...ows-excel.html see posts 6 and 13), which should speed up your file considerably. (especially if the cleanup of mapping for salary tracker need be performed only once).