Kevin has attended:
Excel Advanced course
Hlookup/cell reference
Hi
I am trying to using Hlookup in a number of cells. But row was set for a specific number, for example 5. When I drag the formula to other cells, the row number will not change. I donot want to change one by one in each cell and I know there is way to reference the data with number in table. Would you please kindly show me how this could be done without changing row number?
Thanks
RE: Hlookup/cell reference
Hi Kevin,
Thank you for the forum question.
You have a number of ways doing what you want.
If you just type the number (5) then as you have experienced it will not change when you drag the formula.
What I do:
If I need to drag it down I use a column to control the row numbers. I type 5 in the first cell, 6 in the next cell & 7 in the next cell. Instead of typing 5 in the formula I put in the cell reference to the cell I typed 5 in. When I drag it down Excel will follow the direction down. So in the cell under the start cell Excel will return the information from row 6.
You can also use the Match function to return the row number if you have headings you can match.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
