• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Vlookup take lot of time to calculate

A

Abhijeet

Active Member
Hi
I have data is in excel till 200000 rows vlookup take lot of time to looking that value.please give me macro to fast give the result
 

Attachments

  • Vlookup macro.xlsx
    123.9 KB · Views: 2
Hi Abhijeet ,

In case you are doing some extra processing beyond just looking up each item in the first table , and in case you can sort the first table in ascending order , you can use a formula such as :

=IF(B2=VLOOKUP(B2,Sheet1!$A$1:$A$150000,1,1),VLOOKUP(B2,Sheet1!$A$1:$A$150000,1,1),"")

Here , the 4th argument in the VLOOKUP function is 1 , which means an approximate match is being done ; the value returned through the approximate match is checked against the looked-up item to see if it is an exact match.

Thus , even though there are 2 matches in the above formula , since the lookup range is sorted , and the 4th argument is 1 , the search should be fast.

Can you try this and post back ?

Narayan
 
=IF(B2=VLOOKUP(B2,Sheet1!$A$1:$A$150000,1,1),VLOOKUP(B2,Sheet1!$A$1:$A$150000,1,1),"") this formula work only 1st cell remaining cells are nothing show only blank
=vbalookup(B2,Sheet1!$A$1:$A$9999,1) formula range is 9999 only i have data is till 200000 rows
 
You must log in or register to reply here.
Back
Top