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

 

[human2014]

Thank you all!

Is there a way to create a macro that sorts column O to a specific name between the 7 names I have and then copy from column A to K into a new excel file ?

 

[human2014]

I still get a slow excel (it write processing)

=VLOOKUP($C2022,employeeid,1,FALSE)
=IFERROR(VLOOKUP(S2022,LMrng,2,FALSE),"-")
=IFERROR((VLOOKUP(S2022,mapping,2,FALSE)),"-")
=IFERROR((VLOOKUP(S2022,flp,2,FALSE)),"-")
=VLOOKUP(VALUE(A2022),Sheet2!$A:$C,3,0)

Any new ideas ?

 

[Aladin Akyurek]

I still get a slow excel (it write processing)

=VLOOKUP($C2022,employeeid,1,FALSE)
=IFERROR(VLOOKUP(S2022,LMrng,2,FALSE),"-")
=IFERROR((VLOOKUP(S2022,mapping,2,FALSE)),"-")
=IFERROR((VLOOKUP(S2022,flp,2,FALSE)),"-")
=VLOOKUP(VALUE(A2022),Sheet2!$A:$C,3,0)

Any new ideas ?

Are you able to sort in ascending order the relevant ranges?

 

[human2014]

It doesn't change anything when I do that.

 

[West Man]

It doesn't change anything when I do that.

But if you can, it opens up some options. Aladin can provide alternative formulas that are longer but more efficient.

 

[Aladin Akyurek]

It doesn't change anything when I do that.

You are wrong at that...

Sort employeeid in ascending order then invoke:

=IF(VLOOKUP($C2022,employeeid,1,1)=$C2022,VLOOKUP($C2022,employeeid,1,1),"-")

Sort LMrng in ascending order on its first column then invoke:

=IF(VLOOKUP(S2022,LMrng,1,1)=S2022,VLOOKUP(S2022,LMrng,2,1),"")

Sort mapping in ascending order on its first column then invoke:

=IF(VLOOKUP(S2022,mapping,1,1)=S2022,VLOOKUP(S2022,mapping,2,1),"")

Sort flp in ascending order on its first column then invoke:

=IF(VLOOKUP(S2022,flp,1,1)=S2022,VLOOKUP(S2022,flp,2,1),"")

Sort Sheet2!$A:$C in ascending order on column A then invoke:

=IF(VLOOKUP(A2022+0,Sheet2!$A:$A,1,1)=A2022+0,VLOOKUP(A2022+0,Sheet2!$A:$C,3,1),"-")

 

[West Man]

Aladin, couldn't the first one just be: =IF(VLOOKUP($C2022,employeeid,1,1)=$C2022,$C2022,"-")

 

[Aladin Akyurek]

Aladin, couldn't the first one just be: =IF(VLOOKUP($C2022,employeeid,1,1)=$C2022,$C2022,"-")

Indeed. Thanks for pointing that out.

 

[human2014]

Is it possible to change the vlookup to match?

I heard that match is more quick than vlookup

Indeed. Thanks for pointing that out.

 

[Aladin Akyurek]

Is it possible to change the vlookup to match?

I heard that match is more quick than vlookup

I guess you do not intend to work with sorted ranges...

Rewriting...

=IFERROR(VLOOKUP(S2022,LMrng,2,FALSE),"-")

in terms of ISNUMBER/MATCH below:

=IFERROR(INDEX(LMrng,MATCH(S2022,INDEX(LMrng,0,1),0),2),"")

won't yield a speed difference if any.

Let's suppose that LNrng = A2:B1000.

In X2022 enter:

=MATCH(S2022,$A$2:$A$1000,0)

In Y2022 enter, which constitutes the result cell:

=IF(ISNUMBER(X2022),INDEX($B$2:$B$1000,X2022),"-")

The foregoing two-range set up could be somewhat faster than:

=IFERROR(VLOOKUP(S2022,$A$2:$B$1000,2,0),"-")