Combo Formula using....IF ISERROR INDEX MATCH - Software Applications - MS Excel - TIK Share
1 Likes 1626 Views

# Combo Formula using....IF ISERROR INDEX MATCH

#### CA Prabhaharan N

2013-06-30

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.

=IF(ISERROR(INDEX(Data!\$B\$5:\$B\$29,MATCH(\$B3,Data!\$C\$5:\$C\$29,0),0)),0,(INDEX(Data!\$B\$5:\$B\$29,MATCH(\$B3,Data!\$C\$5:\$C\$29,0),0))

Benifits:

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.

Limitations

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

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

#### News Reporter

2013-07-01

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.