+ Reply to Thread
Results 1 to 8 of 8

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

  1. #1
    Registered User
    Join Date
    03-09-2006
    Location
    Australia
    Posts
    80

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

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

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    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?

    Quote Originally Posted by 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)?

  3. #3
    Registered User
    Join Date
    03-09-2006
    Location
    Australia
    Posts
    80
    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.

  4. #4
    Registered User
    Join Date
    03-09-2006
    Location
    Australia
    Posts
    80
    BTW, tried F9 to no avail.

  5. #5
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    Do you mean asin

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


    Quote Originally Posted by 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.

  6. #6
    Registered User
    Join Date
    03-09-2006
    Location
    Australia
    Posts
    80
    Exactly. I may be mssing something really simple here, but just cannot see the wood for the trees

  7. #7
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,195
    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?

    Quote Originally Posted by AussieExcelUser
    Exactly. I may be mssing something really simple here, but just cannot see the wood for the trees

  8. #8
    Registered User
    Join Date
    03-09-2006
    Location
    Australia
    Posts
    80
    My reply eluded you because I completely missed your point, sorry about that.

    Bryan, you champ! That is exactly what I was after, thank you very much.

    Cheers,

    Grant.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1