vlookup taking long time

[vinod9111]

HI All,

I have two three sheets of data which run into 60000 rows and i need to do a vlookup from other file which too has similiar kind of records. It take a lot of time to produce the result, as it keeps on calculating. Is there any other way to do it , so that it can be faster.

Please help

regards,

Vinod

 

[Aladin Akyurek]

HI All,

I have two three sheets of data which run into 60000 rows and i need to do a vlookup from other file which too has similiar kind of records. It take a lot of time to produce the result, as it keeps on calculating. Is there any other way to do it , so that it can be faster.

Please help

regards,

Vinod

What is the formula you are invoking?

 

[vinod9111]

=VLOOKUP(B2,'[master.xls]Direct'!$A$2:$C$24022,3,0)

 

[Aladin Akyurek]

=VLOOKUP(B2,'[master.xls]Direct'!$A$2:$C$24022,3,0)

Are you able/allowed to sort $A$2:$C$24022 on column A in ascending order and keep it sorted?

 

[vinod9111]

Yes i can do that.

 

[Aladin Akyurek]

Yes i can do that.

Then we can invoke a faster formula...

Either:

=IF(VLOOKUP(B2,'[master.xls]Direct'!$A$2:$A$24022)=B2,
   VLOOKUP(B2,'[master.xls]Direct'!$A$2:$C$24022,3),"Not Found")

Or:

=IF(LOOKUP(B2,'[master.xls]Direct'!$A$2:$A$24022)=B2,
    LOOKUP(B2,'[master.xls]Direct'!$A$2:$A$24022,
       '[master.xls]Direct'!$C$2:$C$24022),"Not Found")

 

[vinod9111]

Hi alladin,

I tried the first formula but it thrown some error of "entered few arguments for this function".

did i miss something

regards

 

[Aladin Akyurek]

Hi alladin,

I tried the first formula but it thrown some error of "entered few arguments for this function".

did i miss something

regards

Missing parameterts...

=IF(VLOOKUP(B2,'[master.xls]Direct'!$A$2:$A$24022,1,1)=B2,
   VLOOKUP(B2,'[master.xls]Direct'!$A$2:$C$24022,3,1),"Not Found")

The LOOKUP version doesn't need such parameters.