Excel Report – Do You Make this Mistake when Writing Lookup Formulas?

One of the errors I have committed again and again when writing lookup formulas, is forgetting to set the reference of the arrays to absolute.

For example: I write the formula VLOOKUP(A2,A2:D11,1,0) instead of this one VLOOKUP(A2″,$A$2:$D$11,1,0)

Here’s a brief and recurrent story…

I finished the formula, I tested and it worked. Then, after I copied and pasted the formula downward, it didn’t work anymore. The #N/A wallpaper appeared!

The reason of this crash is very simple; your array is now pointing to a dislocated data set. You must  anchor it first.

Make this procedure a kind of law so you can follow it on a regular basis…

Consider the arrays involved in the lookup formulas as fixed data sets so apply the absolute reference to keep them secure in all the lookup formulas.

Here are a few examples:

  • =VLOOKUP(“*”&A2&”*”,$A$2:$D$11,1,0)
  • {=INDEX($A$2:$E$12,MATCH(A2&B2,$A$2:$A$12&$B$2:$B$12,0),5)}
  • SUMIF($D$2:$D$8,A2,$B$2:$B$8)
  • =SUMPRODUCT((MONTH($B$2:$B$7)=A2)*$C$2:$C$7)

Conclusion.

Why an entire article for such simple thing? These tiny things are usually overlooked. The price we pay is high, unnecessary frustration, waste of time, lack of credibility. My strong recommendation is don’t overlook it…

Have you given  priority to this small step?