vlookup

[david gabra]

Hi
Is there a way to dramatically speed up vlookup
I have 2 files, each with 130,000 lines.
I need to vlookup to compare data, and it is slow slow,
Can I speed up vloookups, and how please?

 

[Chihiro]

One way is to use PowerQuery (or MS Query) to perform your lookup operation.

There are other ways as well, but hard to tell without knowing your data structure.

 

[narunfca]

Combination of INDEX and MATCH function is much faster than VLOOKUP function.

with best regards
Arun N

 

[Chihiro]

@narunfca

Not necessarily true. For small/medium data set (around 10,000 rows), it performs approx same. And if you use double approximate match VLOOKUP, you can get faster results.

https://exceljet.net/formula/faster-vlookup-with-2-vlookups

However, do note that it requires the list to be sorted by "Key" or it may give wrong result.

See below article for good comparison of various methods.
http://analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/

 

[david gabra]

can you show me based on my attachment- sorry I did not understand the article.

Not necessarily true. For small/medium data set (around 10,000 rows), it performs approx same. And if you use double approximate match VLOOKUP, you can get faster results.

https://exceljet.net/formula/faster-vlookup-with-2-vlookups

 

[david gabra]

what does "key" mean?

 

[Chihiro]

Where's your attachment?

Key = LookUp value

So sort your table on left most column (which you are using to do lookup based on key).

For illustrated example, see the difference between "VLOOKUP Example" and "DOUBLE TRUE VLOOKKUP" found in link below.

http://analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/

This site shows detailed example of formula construction as well.

 

[david gabra]

Where's your attachment?

Key = LookUp value

So sort your table on left most column (which you are using to do lookup based on key).

For illustrated example, see the difference between "VLOOKUP Example" and "DOUBLE TRUE VLOOKKUP" found in link below.

http://analystcave.com/excel-vlookup-vs-index-match-vs-sql-performance/

This site shows detailed example of formula construction as well.

 

[david gabra]

I added the attachment - I would appreciate a practical example.

 

[david gabra]

Please can any one help.

 

[Chihiro]

To get faster help. Explain in full detail of what you need.

Since you lack unique identifier (key) that ties 2 table together, you can't use Double True VLOOKUP method here.

You can use helper column, or if you have access to it, PowerQuery to do your operation.