Hlookup question

[wbaid]

Hello,

I am using Hlookup to get the information from one tab to another the formula i am using is
=HLOOKUP(G51,'FY09 Sheet1'!F54:Q175,4)

The problem is when i drag the formula the row index number doesn't change from 4 to 5 instead i have to change in every row the row index number manually, is there anyway we can change it automatically because i don't have manually change it 1000 lines of information I have.

Any help will be appreciated.

Thank you

 

[Gettingbetter]

Hi Wbaid

Welcome to the board,

try

=HLOOKUP(G51,'FY09 Sheet1'!F54:Q175,ROW())

Cheers
GB

 

[Gettingbetter]

You might also want to put $ signs in your range so it doesn't change

=HLOOKUP(G51,'FY09 Sheet1'!$F$54:$Q$175,ROW())

Cheers
GB

 

[wbaid]

Gettingbetter,

As you told me i did ROW() but it doesn't do any thing do i need to put row numbers? Also i want it to go from 3 and next 4 next 5 etc as i drag it.

 

[wbaid]

I tried putting ROW(3) but it gives me any error. Any suggestions?

 

[Gettingbetter]

Hello,

Row()

Will return the row number you are on,

so if you are on row 5 then row() = 5

Therefore when you drag it down the row number will change. If the numbers you need do not correspond with the row you are on, a simple + or - will sort it eg if row 6 needs to bring back the lookup value 4, then I would use Row()-2

Hope this helps
GB

 

[wbaid]

Gettingbetter,

You are genius, It works. Thank you very very very much.

 

[barry houdini]

Possibly better to use INDEX and MATCH, e.g.

=INDEX('FY09 Sheet1'!F57:Q57,MATCH(G51,'FY09 Sheet1'!F$54:Q$54))

Like HLOOKUP with a row index of 4 this will match with row 54 and return a value from row 57....but when you copy down the return range will increment by 1 row at a time.

Allows you to delete rows, for instance, without affecting the result

Note: using HLOOKUP with no 4th argument (or MATCH with no 3rd argument) means that your lookup range must be sorted ascending and you may get a "closest match". If you only want an exact match change to

=INDEX('FY09 Sheet1'!F57:Q57,MATCH(G51,'FY09 Sheet1'!F$54:Q$54,0))

 

[wbaid]

barry houdini,

I think its to advance for me because i don't understand what you are doing, if it possible for you to explain in details? If not thats fine.

Thanks