+ Reply to Thread
Results 1 to 11 of 11

Lookup with vertical and horizontal criteria

  1. #1
    Registered User
    Join Date
    08-22-2013
    Location
    cebu,philippines
    MS-Off Ver
    Excel 2010
    Posts
    44

    Lookup with vertical and horizontal criteria

    Hi guys,

    Need your help here, Please see attached for a clearer view.
    THANK YOU......
    Attached Files Attached Files
    blessed are those who share whats theirs

  2. #2
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Lookup with vertical and horizontal criteria

    Try this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Please add reputation by clicking on the * if I have helped.
    Please mark the thread SOLVED if your issue has been resolved.
    Thanks, Glenn.

  3. #3
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup with vertical and horizontal criteria

    Here is your formula

    =SUMPRODUCT(($A$4:$A$13=O3)*($B$4:$B$13=P3)*($C$3:$L$3=Q3)*($C$4:$L$13))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne

  4. #4
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Lookup with vertical and horizontal criteria

    Quote Originally Posted by AlKey View Post
    Here is your formula

    =SUMPRODUCT(($A$4:$A$13=O3)*($B$4:$B$13=P3)*($C$3:$L$3=Q3)*($C$4:$L$13))
    I tried a sumproduct first and got a #VALUE! error. This is the formula I used. Can you see why it wouldn't work?
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup with vertical and horizontal criteria

    Quote Originally Posted by gak67 View Post
    I tried a sumproduct first and got a #VALUE! error. This is the formula I used. Can you see why it wouldn't work?
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you tried it and it gave you an error it means that you did something wrong.

    here is the result

    O
    P
    Q
    R
    S
    2
    ACCT CENTER MONTH
    3
    510200
    15H001-510 JAN
    200
    4
    520300
    15H001-511 FEB
    410
    5
    510200
    15H001-520 MARCH
    720
    6
    520300
    15H001-521 APR
    930
    7
    510100
    18H001-510 MAY
    140
    8
    510300
    18H001-511 JUNE
    350
    9
    520400
    18H001-512 JULY
    560
    10
    510100
    18H001-520 AUG
    670
    11
    510300
    18H001-521 SEPT
    880
    12
    520400
    18H001-522 OCT
    1090
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Lookup with vertical and horizontal criteria

    Yes I know your formula gives the required result. I was under the impression that the -- before the criteria was the same as multiplying the criteria in a sumproduct function, but one (yours) gives the correct answer and the other (mine) doesn't. I was hoping for more insight than being told I did something wrong.

    I have however now done my own research and when using 2 different axis in a sumproduct the -- option does not work.

  7. #7
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup with vertical and horizontal criteria

    @gak67

    I must have misread your comments and I thought that you were referring to my formula that is giving an error (it actually does happen. Please accept my apology as I had no intention to offend you in any way.

  8. #8
    Valued Forum Contributor
    Join Date
    04-22-2014
    Location
    Auckland, New Zealand
    MS-Off Ver
    Office 365 (work) and Excel 2013 (home)
    Posts
    1,167

    Re: Lookup with vertical and horizontal criteria

    Wasn't offended (maybe a little miffed ), but thanks for the apology.

  9. #9
    Forum Contributor
    Join Date
    05-04-2014
    Location
    New Jersey, United States
    MS-Off Ver
    Excel 2010, 2013
    Posts
    120

    Re: Lookup with vertical and horizontal criteria

    Here's an OFFSET/MATCH option (also with ctrl+shift+enter):

    =OFFSET($A$3,MATCH(O3&P3,A$4:A$13&B$4:B$13,),MATCH(Q3,B$3:L$3,))

  10. #10
    Registered User
    Join Date
    08-22-2013
    Location
    cebu,philippines
    MS-Off Ver
    Excel 2010
    Posts
    44

    Re: Lookup with vertical and horizontal criteria

    Hi Guyz,

    you all have a brilliant minds, thanks for this,
    chil guyz.....l

  11. #11
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Lookup with vertical and horizontal criteria

    Quote Originally Posted by clabulis View Post
    Here's an OFFSET/MATCH option (also with ctrl+shift+enter):

    =OFFSET($A$3,MATCH(O3&P3,A$4:A$13&B$4:B$13,),MATCH(Q3,B$3:L$3,))
    You can also do it without Ctrl+Shift+Enter

    =OFFSET($A$3,INDEX(MATCH(O3&P3,A$4:A$13&B$4:B$13,),),MATCH(Q3,B$3:L$3,),)

+ 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. [SOLVED] VBA Vertical and horizontal Lookup ???
    By ciapul12 in forum Excel Programming / VBA / Macros
    Replies: 19
    Last Post: 03-07-2014, 06:15 PM
  2. Lookup Table Using Horizontal and Vertical Criteria
    By bbmonkey87 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 03-06-2014, 06:31 PM
  3. [SOLVED] Horizontal and Vertical Lookup
    By kingkong321 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-09-2014, 06:48 AM
  4. [SOLVED] Lookup a vertical and horizontal value
    By KARENKERBA in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-28-2013, 04:33 PM
  5. Horizontal and Vertical lookup.
    By rlsublime in forum Excel General
    Replies: 3
    Last Post: 09-05-2012, 05:54 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