17

I am comparing values in a row in one sheet to values in another row in another sheet. The following formula and works:

=IFERROR(VLOOKUP(A1,Sheet1!A1:A19240,1,FALSE),"No Match")

My problem is when I fill down the formula, it increments A1 correctly but also increments the (A1:A19240), so half way down I have narrowed the search field.

How can I apply this formula to a column?

pnuts
  • 58,317
  • 11
  • 87
  • 139
Andrew
  • 343
  • 1
  • 3
  • 9

1 Answers1

33

Change A1:A19240 to A$1:A$19240, i.e. apply: 

=IFERROR(VLOOKUP(A1,Sheet1!A$1:A$19240,1,FALSE),"No Match")

This is called using absolute references.

TylerH
  • 20,799
  • 66
  • 75
  • 101
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 1
    Hint/Tip: Edit the formula, put your cursor on the range and hit F4. This will toggle absolute. Keep toggling with F4 and you can anchor one side of the range. – Jim L Feb 10 '20 at 21:19