+ Reply to Thread
Results 1 to 6 of 6

HLookup ( Horiz.Zoeken ) doesn't find the first choise from a list

  1. #1
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    HLookup ( Horiz.Zoeken ) doesn't find the first choise from a list

    Hi,

    I noticed this a few version back, last week, from Excel 2016, that when I got a table and do a HLookup, using a popup list, then the first Item is not found in the list and copied into the cells.

    If I make the table reference smaller, meaning leave the row title (first column) out, then it works. But I can't do that because somewhere else I need the first column to search for the row title.

    Any suggestions?

    I got now at least 8 or 9 WB not working correctly.
    Attached Files Attached Files

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: HLookup ( Horiz.Zoeken ) doesn't find the first choise from a list

    In D4 you have this:

    =HLOOKUP($C$2,Tabellen!$B$2:$N$15,3)

    and in D5 this:

    =HLOOKUP($C$2,Tabellen!$A$2:$N$15,2)

    which is wrong. Change it to this:

    =HLOOKUP($C$2,Tabellen!$B$2:$N$15,2)

    D6 to D16 need changing, too.

    Excel 2016 (Windows) 32 bit
    B
    C
    D
    2
    **Sterkteklasse :
    **C14
    **
    3
    **Klimaatklasse :
    **1
    **
    4
    **E0,mean [kN] =
    **7
    5
    **fm,k =
    **14
    6
    **?mean =
    **350
    7
    **?k =
    **290
    8
    **ft;0;k =
    **7.20
    9
    **ft;90;k =
    **0.40
    10
    **fc;0;k =
    **16
    11
    **ft;90;k =
    **2.0
    12
    **fv;k =
    **3.0
    13
    **E0,05 =
    **4.7
    14
    **E90,mean =
    **0.23
    15
    **Gmean =
    **B
    16
    **G0,05 =
    **0.29
    Sheet: Rekenblad
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  3. #3
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: HLookup ( Horiz.Zoeken ) doesn't find the first choise from a list

    Thank you,
    but as I wrote at that sheet, that when I make the table reference smaller the it works, hence the D4/D5 difference. It was to show that that change do get it work.
    I did wrote that on that page.

    So why is =HLOOKUP($C$2,Tabellen!$A$2:$N$15,2) not working?
    It search for 'C14' and cant find it, but 'C16' it can be found.

    Somewhere else I need the first column its value so I can't remove that 'A' column.
    Last edited by lord anubis; 03-24-2018 at 07:10 AM.

  4. #4
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: HLookup ( Horiz.Zoeken ) doesn't find the first choise from a list

    You need this:

    =HLOOKUP($C$2,Tabellen!$A$2:$N$15,3,0)

    This forces an exact match.

    Your locale will probably need this:

    =HORIZ.ZOEKEN($C$2;Tabellen!$A$2:$N$15;3;0)
    Last edited by AliGW; 03-24-2018 at 07:24 AM.

  5. #5
    Forum Contributor
    Join Date
    07-16-2009
    Location
    DutchLand
    MS-Off Ver
    Excel MacOS 16
    Posts
    161

    Re: HLookup ( Horiz.Zoeken ) doesn't find the first choise from a list

    Thank you, why did I forgot that.
    I thought, just today apparently , the default was 'FALSE' ( '0') instead of 'TRUE' '1'
    My bad.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,348

    Re: HLookup ( Horiz.Zoeken ) doesn't find the first choise from a list

    You're welcome!

    For what it's worth, I always specify the fourth argument.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

+ 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. VLOOKUP - Vert.zoeken
    By Manado in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 03-10-2018, 03:42 AM
  2. [SOLVED] HLOOKUP - Doesn't seem to like me :(
    By EddieMcr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2015, 07:20 AM
  3. Replies: 5
    Last Post: 10-17-2013, 08:03 PM
  4. HLookup Formula doesn't work
    By Screechpt in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-14-2013, 12:36 PM
  5. HLOOKUP Help. HLOOKUP links to Drop down list problems
    By finalmike in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-02-2013, 07:08 PM
  6. [SOLVED] Extending Horiz. Axis ?
    By Robert1111 in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-22-2012, 09:23 AM
  7. [SOLVED] Macro to start AFTER choise from validation drop down list
    By rpinxt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-26-2012, 09:26 AM

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