+ 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 2019
    Posts
    133

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,649

    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" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

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

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,649

    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 2019
    Posts
    133

    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
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    63,649

    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