How to change cell reference in a HLookup when copying formula vertically

[AussieExcelUser]

Is there a way to have a cell reference automatically change in a HLookup formula when copying the formula vertically (as it would if it was a VLookup)?

[Bryan Hessey]

I thought a HLookup and VLookup (like all formula) did this. What is your specific formula and the problem seen in other cells?

note, did you press F9 if you are on Manual update?

Is there a way to have a cell reference automatically change in a HLookup formula when copying the formula vertically (as it would if it was a VLookup)?

[AussieExcelUser]

The formula is very generic: =HLookup(A2,'Different Sheet'!$A$1:$A$Z100,2,0)

What I am after is a way of automatically advancing the column number from where the returned value is gained (2 to 3 to 4 etc) when copying this formula vertically (ie, down a column). At the moment nothing in the formula changes at all when copying it down a column.

[AussieExcelUser]

BTW, tried F9 to no avail.

[Bryan Hessey]

Do you mean asin

=HLookup(A2,'Different Sheet'!$A$1:$A$Z100,Row()+1,0)

The formula is very generic: =HLookup(A2,'Different Sheet'!$A$1:$A$Z100,2,0)

What I am after is a way of automatically advancing the column number from where the returned value is gained (2 to 3 to 4 etc) when copying this formula vertically (ie, down a column). At the moment nothing in the formula changes at all when copying it down a column.

[AussieExcelUser]

Exactly. I may be mssing something really simple here, but just cannot see the wood for the trees

[Bryan Hessey]

That reply eludes me.

If you use Row() you will increment with each row. Your formula stated 2 for the row_index_number when A2 was the Lookup_Value thus I presumed that Row()+1 would be what you wanted, ie, pick up colun 3 for row 2, column 4 for row 3 etc.

Is this correct?

Exactly. I may be mssing something really simple here, but just cannot see the wood for the trees