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

 

[West Man]

Try to use actual ranges for your VLOOKUPs rather than full columns

 

[Gingertrees]

I've seen this sort of this before.
Two things that can speed it up:
1) By using a "helper column" to perform the =RIGHT(a179985,7) calculation ahead of time.

For example, say your helper column is in column Z. So Z179985 contains formula =RIGHT(A179985,7). Then use column Z cell in your formula:
=iferror(vlookup(z179985, LMrng, 2, false),"-")
(I'll explain LMrng next...)

2) instead of using lookup range of entire columns, create a named range and use that.

For example, instead of referring to Mapping for Salary Tracker sheet, range column L thru M, select the used range of L:M in there. For example, say it's L2:M200000. Call this range "LMrng".

Put the two together, and you get
=iferror(vlookup(z179985, LMrng, 2, false),"-")

Do this for all of your lookup ranges. Give them meaningful names like Salaryrng, OPrng, etc. Don't name them "Fred" "Alice" ...
This will speed things up.

 

[henrik2h]

Using Index(Match(.... also tend to be faster.

If you convert it to a data table the references will adjust automatically, otherwise you might need to have a formula statement for your named range if it increases over time.

 

[alansidman]

CROSSPOSTED: vlookup - excel slow

 

[human2014]

I like those ideas but the file has more than 365000 rows.
How can I name a range ?
I want to replace vlookup with index and match but I get an error everytime

 

[oldbrewer]

your first iferror formula appears to be = iferror(vlookup(something, somewhere, 2, false)), "-"

if it is not an error there is nothing for it to do

eg =if(iserror(a1/a2),"error","ok")

 

[human2014]

So you are saying I should replace it ?

your first iferror formula appears to be = iferror(vlookup(something, somewhere, 2, false)), "-"

if it is not an error there is nothing for it to do

eg =if(iserror(a1/a2),"error","ok")

 

[Gingertrees]

your first iferror formula appears to be = iferror(vlookup(something, somewhere, 2, false)), "-"

if it is not an error there is nothing for it to do

eg =if(iserror(a1/a2),"error","ok")

He does have an error there, but I think OldBrewer is confusing the IF function with the IFERROR function.
OP just had an extra parentheses.

Refer to my tips above, and note that there's just a single ")" after "False" in the vlookup.
=iferror(vlookup(z179985, LMrng, 2, false),"-")

And yes, you can have a named range that is 1000s of rows long.

 

[human2014]

How do you name a range ?

He does have an error there, but I think OldBrewer is confusing the IF function with the IFERROR function.
OP just had an extra parentheses.
[/COLOR]
Refer to my tips above, and note that there's just a single ")" after "False" in the vlookup.
=iferror(vlookup(z179985, LMrng, 2, false),"-")

And yes, you can have a named range that is 1000s of rows long.