Question: Why do I get #N/A when I drag the vlookup formula down?

By | February 2, 2015

If your formula started working at the top of the table but when you dragged it down it started returning #N/A then the solution follows…

For example your formula might’ve looked something like  =vlookup(D1, A2:C7, 3, FALSE)

When you drag the formula down, the “A2:C7” part will move down too and become “A3:C8”, “A4:C9” etc

To stop this happening there are two potential fixes:

  1. Make the reference absolute – $A$2:$C$7 which you can do by highlighting that part of the formula and pressing F4 or just adding the $ signs in manually; or
  2. If there’s nothing else below your table, you can highlight the entire columns so that instead of “A2:C7” you’ll have “A:C”

Leave a Reply

Your email address will not be published. Required fields are marked *