+ Reply to Thread
Results 1 to 6 of 6

Hlookup not working, need something else to lookup rows and columns

  1. #1
    Forum Contributor
    Join Date
    08-22-2009
    Location
    Manitoba
    MS-Off Ver
    Office 2010
    Posts
    524

    Hlookup not working, need something else to lookup rows and columns

    I have have these names in rows alternating with numbers. I have a hlookup formula that searches a row and finds the value and adds it up for each row, BUT if there is more than one instance of the word, then it only finds the first and not anymore beyond that. So I am hoping someone with much more knowledge can provide a formula or even VBA for this to work, IF at all possible. In the attached spreadsheet, WBNB and cell O2, does not add up, nor do the rest. Thanks in advance.
    Attached Files Attached Files
    Last edited by thecdnmole; 12-03-2020 at 04:14 AM.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hlookup not working, need something else to lookup rows and columns

    You can use SUMPRODUCT:

    =SUMPRODUCT(--($A$2:$L$20=N2),$A$3:$L$21)

    copied down. Note the ranges OVERLAP.
    Attached Files Attached Files
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Expert
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,490

    Re: Hlookup not working, need something else to lookup rows and columns

    Looks like you should be able to use the following.

    O2:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    However, there are entries in A:L with no corresponding entry in col N, e.g., PB/CBV mix in F2 or Pb floor sweep in I2.

  4. #4
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Hlookup not working, need something else to lookup rows and columns

    Please try at O2

    =SUMIFS($A$3:$L$21,$A$2:$L$20,N2)
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    08-22-2009
    Location
    Manitoba
    MS-Off Ver
    Office 2010
    Posts
    524

    Re: Hlookup not working, need something else to lookup rows and columns

    THANKS to Glenn, hrlngrv and Bo_Ry! All three work perfectly!

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,893

    Re: Hlookup not working, need something else to lookup rows and columns

    You're welcome and thanks for the rep.

+ 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 finding rows above lookup value.
    By GPMan in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-15-2018, 09:49 AM
  2. Selecting table columns and rows isn't working
    By phpolicylady in forum Excel General
    Replies: 1
    Last Post: 07-05-2017, 04:53 PM
  3. HLOOKUP not working...
    By IntisarN in forum Excel General
    Replies: 3
    Last Post: 01-29-2015, 04:26 PM
  4. SUMIF Rows and Columns Not working
    By SHUTTEHFACE in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 09-29-2014, 05:59 AM
  5. Replies: 3
    Last Post: 04-08-2014, 03:11 AM
  6. HLOOKUP not working
    By mufan in forum Excel General
    Replies: 2
    Last Post: 01-04-2010, 03:14 PM
  7. Lookup using columns and rows
    By penfold in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-21-2009, 05:09 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