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.