Forum Discussion
Solved
Using ISERROR to remove #NA, SUM doesn't give correct results
I am using VLOOKUP to pull information from one worksheet to another. The formula works, but because there are several #NA, which won't allow me to sum the column, I used ISERROR to replace the #NA ...
ECJ53 Since you dragged the VLOOKUP function down in column G, you also pick-up values for the rows that are NOT sub-totals, and you are thus double counting several entries (demonstrated in the picture below), explaining a sum higher than expected. Remove the amounts on all rows that are not sub-totals and the result will be correct.
With regard to the formula itself, you may want to use this one in stead:
=IFERROR(VLOOKUP(A5,Data_Set,8,FALSE),0)
Thank you, that was the issue.
One question? This spreadsheet was only a few lines but if I had a spreadsheet with a hundred rows or more, how would you copy the formula down the column and NOT pick up the additional rows?
I thought I had dragged it in the Total mode, not in the full worksheet mode?
Thanks
EJ
Copy the cell containing your formula (Ctrl+C). Then, select your destination range. On the home tab, click the find and select button in the editing group (far right). In the dialog box select "visible cells only." Then paste (Ctrl+V).