1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Lookup last value using 2 criteria

Discussion in 'Ask an Excel Question' started by Busymanjohn, Apr 20, 2017.

  1. Busymanjohn

    Busymanjohn Member

    Messages:
    164
    Hi Guys, I am having problems with a formula, I am trying to lookup a range of cells and return the last value in that range based on 2 criteria ,,, I can return the last value based on 1 criteria using LOOKUP(2,1/(B3:B16=F6),C3:C16), but how do I insert the 2nd criteria ,,,, also looked at Index & Match, doesn't quite get me the result ( although I could be missing something ). Sample file attached ..... the results I want to appear in cells G6 and G7 ,,, range is in A3:C16, criteria is F2 and F6 ( time range and date ). Any ideas?

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,524
    G6: =INDEX($C$4:$C$16,SUMPRODUCT(--MAX(($A$4:$A$16=$F$2)*($B$4:$B$16=F6)*(ROW($A$4:$A$16)-3))))
    copy down
    Thomas Kuriakose likes this.
  3. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,150
    In G6, copy down :

    =LOOKUP(2,1/((A$4:A$16=F$2)*(B$4:B$16=F6)),C$4:C$16)

    Regards
    Thomas Kuriakose likes this.
  4. Busymanjohn

    Busymanjohn Member

    Messages:
    164
    Thanks guys, both solutions worked a treat ..... :)

Share This Page