Many of us will find difficult to pull the data from various files based on our requirement. The data in the various excel file will be in different format. Vlookup will not help in all cases, since you can't look on left side of the column. Similarly using "Index" and "Match" alone will show error like "#NA" if the key number is missing.  If we use the combinations of all formulas, we can overcome the same.


eg. Suppose, we want the stock report like, qty available, Price details, Zone where the qty is lying etc., Assume that your overall stock number is about 20000 + and you want only for 10 parts in the list. The below formula helps you...


Here is the Advance excel formula combinations... using If, Iserror, Index, Match.




1. We can pull the data from any excel files, no need that it should be in same excel file.

2. #NA error will not appear

3. Overcome VLOOKUP errors.


1. Ensure no duplicate numbers/data in "Key Rows"

2. Unique data / Key data should be availalbe in all excel files.


Download the below file to see the formula and test yourself. 

Posted 4 years, 2 months ago by CA Prabhaharan N

Hi Prabhaharan, Thanks for the formulla. but can you edit your article and make it more clear, because the formulla in your excel file just bounced. Can you explain the formula stepwise step. It would be of great help.

Posted 4 years, 2 months ago by News Reporter

Your Reply:

You need to be logged in to reply.