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

Question

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.

Answer 1

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))

Answer 2

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.

Answer 3

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.

Answer 4

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