# Want to use HLOOKUP to drag down the whole column, how can I do this?

See the following screenshot:

This is only an example, my real worksheet is more complicated than this and has hundreds of rows.

My current formula is `=HLOOKUP(A7,A1:D4,2,0)`, but this formula doesn't allow me to drag it down the column so that I can get "2-Jan-12" and "3-Jan-12" for Tom too. How can I change the formula to make it suitable for dragging down?

Update: I tried `=HLOOKUP(\$A\$7,\$A\$1:\$D\$4,2,0)`, but upon dragging down now I get 5 for "2-Jan-12" and "3-Jan-12" as well. I want to drag the formula down so that "2-Jan-12" shows 6 and "3-Jan-12" shows 7.

-

You need to update the 2 also so you can use this version in B8 copied down

`=HLOOKUP(A\$7,\$A\$1:\$D\$4,ROWS(B\$8:B8)+1,0)`

`ROWS` function increments as you drag down and changes the `row_index_num` in `HLOOKUP` to 3, 4 etc.

Note: the above doesn't actually match the dates, it just gets the value from the next row regardless of date, to specifically match dates use this version

`=INDEX(B\$2:D\$4,MATCH(A8,A\$2:A\$4,0),MATCH(A\$7,B\$1:D\$1,0))`

-
brilliant, thanks –  daysandtimes Sep 27 '12 at 13:26

You're on the right track with this. Here's the formula I used:

``````=HLOOKUP(\$B\$1,\$C\$1:\$E\$4,ROW(),FALSE)
``````

`HLOOKUP` looks across your columns and returns the first value \$B\$1 (using \$ to keep the absolute reference). The lookup range also uses absolute values so it doesn't "creep" as you drag and fill the formula. The `ROW` function returns the values in the same row without having to do any extra calculations. I also used data validation to populate the lookup value in \$B\$1 with one of your existing lookup values, giving you a quick drop down to work with. By keeping the "target" value in the same data layout (rows & columns) as your source data, it makes everything simpler to manage (as opposed to your example where it's offset by a number of rows). Good data layout makes almost everything easier in Excel.

-

I'm not 100% sure whehter this is what you're looking for, but to make single columns or rows not change upon dragging, add a `\$` in front of them:

• `A1` will always update based on the direction you drag the cell.
• `\$A1` will only update the row when dragging up/down.
• `A\$1` will only update the column when dragging left/right.
• `\$A\$1` will never change no matter how/where you drag the cell.

For the updated question:

I tested the following formula for the cell `B8`:

``````=HLOOKUP(\$A\$7,\$B\$1:\$D\$4,MATCH(\$A8,\$A\$1:\$A\$4,0),0)
``````

The `MATCH` will determine the n-th row as determined by the date in the first column, which then will pick the correct row from the first table using `HLOOKUP`.

-
i updated the question giving more details, could u pls have a look? –  daysandtimes Sep 27 '12 at 12:58
Added the solution I used - give it a try (works fine for me). –  Mario Sep 27 '12 at 13:16

You need to "fix" the references for the array: `=HLOOKUP(A7,\$A\$1:\$D\$4,2,0)`

-
i tried that, but dragging the formula down gives #N/A –  daysandtimes Sep 27 '12 at 12:56