Vlookup error.

[satish Sanhotra]

Hi Chandoo,

I am facing an error after apply the vlookup. when i mapped the Data range with another data in different sheet in same workbook i get some mapped and unmapped data. but when i apply filter to select the unmapped data i not getting the unmapped data. for your reference i attached the screen shot

 

[Deepak]

Hi @satish Sanhotra,

Instead of pasting such too many snaps why not take pain to uplaod a sample workbook for same.

 

[satish Sanhotra]

File is too Large for uploading That error i am getting while uploading

 

[Deepak]

File is too Large for uploading That error i am getting while uploading

I just asked for sample file.

 

[Somendra Misra]

Hi Satish,

You can see only 10,000 unique items under filter. And #N/A error will come in the last, so if the numbers are more than 10,000 unique entries you will not be able to see them in filters.

Regards,

 

[satish Sanhotra]

I just asked for sample file.

That i am saying the sample file which contain error is too large that form refuse it to be uploaded. even i convert it binary format to reduce the size of file

 

[Somendra Misra]

From the formula which is visible in snaps, it seems you have not locked the table array, in the formula. Click on first formula say in S2, select table_array argument and press F4 to lock it. You will see $ sign in there and then copy down. That might solved the problem.

Regards,

 

[satish Sanhotra]

Hi Somendra,

Category Count
Callable 17656
DND 11163
Repeated Number 2371
Duplicate With Previous Data 583
Invalid 4
Total 31777

I have total data 31777 and from which i mapped 17656 number rest comes in #N/A error as not mapped but i can't find the #N/A Data in the end of filter

 

[Somendra Misra]

Hi Somendra,

Category Count
Callable 17656
DND 11163
Repeated Number 2371
Duplicate With Previous Data 583
Invalid 4
Total 31777

I have total data 31777 and from which i mapped 17656 number rest comes in #N/A error as not mapped but i can't find the #N/A Data in the end of filter

Anything above 10,000 will not be shown.

Regards,

 

[satish Sanhotra]

Yes i not lock table array. but early it is get done from same formula. from different sheet. doing different task of vlookup. tell me any trick to reduce the the excel file size so that i upload the sample file

 

[satish Sanhotra]

Anything above 10,000 will not be shown.

Regards,

Any other option to do this task?

 

[Somendra Misra]

Upload it on dropbox or any other file sharing site, which do not require logging. Than we can have a look at it.

Regards,

 

[Deepak]

Hi satish Sanhotra,

What SM said : http://support.microsoft.com/en-us/kb/295971

Save file in binary format & upload it it cloud like as GDrive, OneDrive, Dropobx, Cubby etc & share the link.

 

[satish Sanhotra]

Thanks SM And Deepak For your Valuable Support . Cause is exactly that my data is vast then 10000 and 10000 data is mapped so it shows only 10000 in filter. Is there is any other option to handle large data and do same operation. I also try to Upload file and same will be shared.

 

[Dayanand]

Hi Satish,

In this formula your table array range is going down while pasting the vlookup formula.

Use "$" in table_array.

regards,
Daya

 

[satish Sanhotra]

Hi all,

This is the share Link of sample file.

https://drive.google.com/file/d/0B8bAtzPiyZzQR3VSbE1ua0FSTmc/view?usp=sharing

 

[Somendra Misra]

Hi all,

This is the share Link of sample file.

https://drive.google.com/file/d/0B8bAtzPiyZzQR3VSbE1ua0FSTmc/view?usp=sharing

In the file, if I filter on #N/A in S column on Sheet1 and try to look for those Mobile numbers in R column in Callable sheet Q column, I do not find those numbers, so that is the reason for those errors.

Regards,