In Excel Video 174, I explained that one of the gotchas in VLOOKUP is that when you copy a VLOOKUP formula, the column reference doesn’t change. We used an Excel function called COLUMN as the column reference so that we could easily copy the VLOOKUP formulas. In Excel Video 176, I’ll show you a very similar trick for HLOOKUP.
Just like VLOOKUP, when you copy an HLOOKUP formula, the row reference won’t update as the rest of the cell references do. Today, we’ll use the ROW function to create a row reference so that when you copy the formula, the row reference will update for you.
Note that I intentionally moved my data range down on the spreadsheet to demonstrate that you can tweak the ROW part of the HLOOKUP formula as necessary. Once I know which row I want to return, it’s easy to modify the ROW part of the formula to be ROW()-7 to get to the row I want. When I copy that formula, the row will always be 7 more than what I need, so consistently subtracting 7 will consistently give me the correct row.
Stay tuned. Next time I’ll take a quick break to show you how the drop-down selection cell in my example works. Once we’ve covered that, we’ll move on to more advanced, more flexible lookup functions.
