I wanted to construct a VLookup situation extensively involving retrieving data from another sheet. I ran into a problem with Excel's hardwired reference adjustment.

To wit, I wanted to have a VLookup where I would have the second row (the first row contains labels) would all refer to the first cell in the second row as the item searched for in the array. I did not want the cells to adjust references as they progressed to the right.

Next, I wanted the VLookup formula to automatically adjust references for subsequent rows by advancing only one row.