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

 

[Gingertrees]

Few ways to do it. Easiest is to highlight the cells you're using. Let's assume based on your post, that your L:M range is L2:M400000. Highlight that range. Now, in the name bar (usually to the left of the formula bar, where it probably says "L2" right now, type "LMrng" and press enter. (Don't include the " ")

Now you can use LMrng anywhere you were using the 'Mapping for Salary Tracker'!$L:$M

Named ranges work across all the sheets in the workbook, so you won't have to call out the name of the sheet anymore. See how nice that is?

 

[human2014]

Thanks you :D

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

Few ways to do it. Easiest is to highlight the cells you're using. Let's assume based on your post, that your L:M range is L2:M400000. Highlight that range. Now, in the name bar (usually to the left of the formula bar, where it probably says "L2" right now, type "LMrng" and press enter. (Don't include the " ")

Now you can use LMrng anywhere you were using the 'Mapping for Salary Tracker'!$L:$M

Named ranges work across all the sheets in the workbook, so you won't have to call out the name of the sheet anymore. See how nice that is?

 

[oldbrewer]

oldbrewer was trying to illustrate a possible missing argument - that's all

 

[Gingertrees]

You can can create named ranges and use them in place of your Mapping for Salary Tracking ranges. And, you still have an extra parenthese.

Here's your formula now:
=IFERROR((VLOOKUP(S2,'Mapping for Salary Tracker'!$L$2:$M$141,2,FALSE)),"-")
and here's how it should look:
=IFERROR(VLOOKUP(S2,'Mapping for Salary Tracker'!$L$2:$M$141,2,FALSE),"-")

If you name L2:M141 as noted above, here's how it COULD look:
=IFERROR(VLOOKUP(S2,LMrng,2,FALSE),"-")

 

[human2014]

Thank you for your help, you were really helpful!

my formulas look like this now:
=VLOOKUP($C2,employeeid,1,FALSE)
=IFERROR(VLOOKUP(S2,LMrng,2,FALSE),"-")
=IFERROR((VLOOKUP(S2,mapping,2,FALSE)),"-")
=IFERROR((VLOOKUP(S2,flp,2,FALSE)),"-")

Is there any vba code that can make it more efficient ?
For example in column O I filter on the name so I can copy the data for that name in a separate excel.
How can I make it do it all in one for the 10 names I have?

You can can create named ranges and use them in place of your Mapping for Salary Tracking ranges. And, you still have an extra parenthese.

Here's your formula now:
=IFERROR((VLOOKUP(S2,'Mapping for Salary Tracker'!$L$2:$M$141,2,FALSE)),"-")
and here's how it should look:
=IFERROR(VLOOKUP(S2,'Mapping for Salary Tracker'!$L$2:$M$141,2,FALSE),"-")

If you name L2:M141 as noted above, here's how it COULD look:
=IFERROR(VLOOKUP(S2,LMrng,2,FALSE),"-")

 

[oldbrewer]

so iferror returns only one thing, what if it is not an error ?

 

[human2014]

My column returns - when there is no data for it.
I guess the iferror is valid there, don't you think ?

so iferror returns only one thing, what if it is not an error ?

 

[Gingertrees]

Old Brewer: Are you in an older version of Excel? IFERROR wasn't added until 2007. It is literally set up like
=IFERROR(VALUE,value if the first part's an error)

Here's some more info:
https://support.office.com/en-us/article/IFERROR-function-c526fd07-caeb-47b8-8bb6-63f3e417f611

 

[oldbrewer]

Old Brewer is using Old Excel 2000

 

[Aladin Akyurek]

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

1.

=VLOOKUP($C179985,'Mapping for Salary Tracker'!$A$2:$A$48,1,FALSE)

>>

Keep it as is or:

=ISNUMBER(MATCH($C179985,'Mapping for Salary Tracker'!$A$2:$A$48,0))

2. Sort 'Mapping for Salary Tracker'!$L:$M on L in ascending order and invoke:

=IF(VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$L:$L,1,1)=RIGHT($A179985,7),VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$L:$M,2,1),"-")

3. Sort 'Mapping for Salary Tracker'!$O:$P on O in ascending order and invoke:

=IF(VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$O:$O,1,1)=RIGHT($A179985,7),VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$O:$P,2,1),"-")

4. Sort 'Mapping for Salary Tracker'!$S:$T on S in ascending order and invoke:

=IF(VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$S:$S,1,1)=RIGHT($A179985,7),VLOOKUP(RIGHT($A179985,7),'Mapping for Salary Tracker'!$S:$T,2,1),"-")

5. Sort Sheet2!$A:$C on A in ascending order and invoke:

=IF(VLOOKUP(A179985+0,Sheet2!$A:$A,1,1)=A179985+0,VLOOKUP(A179985+0,Sheet2!$A:$A,2,1),"-")

Note that sorting in the foregoing proposal is essential.