Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

See the following screenshot:

enter image description here

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.

share|improve this question

4 Answers 4

up vote 2 down vote accepted

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

share|improve this answer
    
brilliant, thanks –  daysandtimes Sep 27 '12 at 13:26

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

Hlookup

=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.

share|improve this answer

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.

share|improve this answer
    
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)

share|improve this answer
    
i tried that, but dragging the formula down gives #N/A –  daysandtimes Sep 27 '12 at 12:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.