VLOOKUP’s a great function, but when you try to copy a VLOOKUP formula, watch out for a gotcha you’ll see in Excel Video 174. When you copy most formulas in Excel, Excel updates the references as you copy. That’s not true with VLOOKUP. When you copy a VLOOKUP formula, the column reference (2 in the video) stays the same.
If you have a VLOOKUP formula you’re going to copy a lot, try using COLUMN. COLUMN is a very simple function that simply returns what column number you’re in. When you copy a COLUMN formula, Excel updates the reference for you. The trick is to combine COLUMN with VLOOKUP. Use COLUMN() to tell Excel which column to return and you can copy that formula all day long. Here’s the structure:
=VLOOKUP(lookup value, table, COLUMN(), FALSE or TRUE)
VLOOKUP is fantastic when the data you’re looking for is in the first column of a table. When your data’s in the first row of the table, you need to use HLOOKUP. We’ll discuss that next.
