+ Reply to Thread
Results 1 to 9 of 9

Error with Hlookup & columns

  1. #1
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Error with Hlookup & columns

    Captureff.JPG

    This is a small portion of an array that I am creating. Its created from pulling from several other arrays.

    =HLOOKUP(D$1,COLUMNS(Values!$D$1:$OQ$132),$A6,FALSE)

    This is the portion that is not working. I don't usually use column function but I took advice that it would help my problem.
    Every time I insert a new column in the values tab the hlookup breaks. It changes the entire array and misses the first column.
    I was told using the columns function would fix that. IT might fix it but I can't even get the columns to work correctly.

    If you need more info please let me know.
    Adam

  2. #2
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Error with Hlookup & columns

    I"m not sure exactly what you are trying to do, or what COLUMNS will add to the mix.

    See: https://support.microsoft.com/en-us/...er%20of%20rows.


    Fast answers need visual help. Please read the yellow banner at the top of this page on how to attach a file and a mocked up solution.
    Trevor Shuttleworth - Retired Excel/VBA Consultant

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


  3. #3
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Re: Error with Hlookup & columns

    =HLOOKUP(D$1,COLUMNS(Values!$D$1:$OQ$132),$A6,FALSE)
    I'm sorry I can't explain better.
    I am using the the columns function in the formula above but its still giving me a N/A
    I would use the normal HLOOKUP layout but every time i insert a new column in my VALUES tab it breaks the formula.

  4. #4
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Re: Error with Hlookup & columns

    Added file.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Re: Error with Hlookup & columns

    I found a work around so I don't need to use the HLOOKUP portion of the formula.

    Can this be solved . I don't see how do to do that.

  6. #6
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2007,2010,365
    Posts
    44,091

    Re: Error with Hlookup & columns

    I am afraid I cannot help you with this. The file has external links to a sharepoint file which I obviously cannot access. And, even if I remove the sharepoint file references, I am left with references to two sheets that don't exist in the sample file.

    As I don't know what is in those sheets, or how they are laid out, I cannot even consider setting up a test bed.

    The formula, even with the external references removed, is far more complex than indictated.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    You would need to provide a copy of the sharepoint file in order to investigate further,

  7. #7
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Error with Hlookup & columns

    COLUMNS(Values!$D$1:$O$132) returns the number of columns in the range, 12.
    HLOOKUP(D$1,COLUMNS(Values!$D$1:$O$132),$A6,FALSE) >> HLOOKUP(D$1,Values!$D$1:$O$132,$A6,FALSE), or
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by protonLeah; 02-02-2023 at 04:36 PM.
    Ben Van Johnson

  8. #8
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Re: Error with Hlookup & columns

    This makes so much more sense now. Thank you so much.!!!

  9. #9
    Registered User
    Join Date
    11-08-2022
    Location
    Clarion, PA
    MS-Off Ver
    365 for Business
    Posts
    21

    Re: Error with Hlookup & columns

    Quote Originally Posted by TMS View Post
    You would need to provide a copy of the sharepoint file in order to investigate further,
    Sorry I cannot do that. These file contain information that is private

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. HLOOKUP uknown Error
    By SAVAGE10489 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-12-2016, 10:51 PM
  2. Ref error with hlookup
    By ammartino44 in forum Excel General
    Replies: 1
    Last Post: 05-01-2015, 05:00 PM
  3. [SOLVED] Hlookup error
    By pleasesmile in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-28-2013, 11:52 AM
  4. [SOLVED] error in HLOOKUP
    By jrtaylor in forum Excel General
    Replies: 2
    Last Post: 07-12-2012, 11:50 AM
  5. HLOOKUP Value not available error
    By lunar_ross in forum Excel General
    Replies: 5
    Last Post: 08-05-2009, 06:27 AM
  6. Error using dates with HLOOKUP
    By Jeff Lowenstein in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  7. [SOLVED] Error using dates with HLOOKUP
    By Jeff Lowenstein in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 08-09-2005, 03:05 PM

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