Results 1 to 9 of 9

Thread: Lookup function to return second last value

  1. #1

    Question Lookup function to return second last value



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hello all,


    I use the attached file for documenting my monitoring observations. In previous Coaching follow up I need the value from last 'Action Plan' for the same agent. The data is in Sheet 2. I tried various formulas but was successful only in getting the current coaching observation. I need the last coaching observation populated in the cell highlighted in green.

    Thanks & regards,
    Nagaraj
    Attached Files Attached Files

  2. #2
    Try this array formula

    =INDEX('Sheet1 (2)'!$E$1:$E$309,MAX(IF('Sheet1 (2)'!$B$1:$B$309=D7,ROW('Sheet1 (2)'!$B$1:$B$309))))

  3. #3
    Hello Bob,

    Thank you for your time and help. The formula returns the first value, I'm looking forward to get last second value. To give a general idea, when I document coaching observation, while in coaching session, I need to recap last coaching session's observations. One particular example, If my current coaching document is 616203140, the observation should be abc9 which in cell E9. Hope you and other members of this forum will be able to help me with this.

    Thank you very much. Appreciate your time.

    Thanks,
    Nagaraj

  4. #4
    Super Moderator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,463
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Did you commit Bob's formula with Ctrl+Shift+Enter?

  5. #5
    Quote Originally Posted by Pecoflyer View Post
    Did you commit Bob's formula with Ctrl+Shift+Enter?
    Hi,

    Thank you for your interest in helping. I tried hitting the key combination after entering the formula, but it's yielding the same result. It's getting me the first value for the record I'm looking for. For emp ID 31002, the value I'm expecting is abc9, which is the second last value. Request further assistance in solving this puzzle.

    Thanks & Regards,
    Nagaraj

  6. #6
    According to your data, there is an entry of abc18 on 6th Aug for 31002, and that is what my formula returns (after tidying up all your data errors).

  7. #7
    Quote Originally Posted by Bob Phillips View Post
    According to your data, there is an entry of abc18 on 6th Aug for 31002, and that is what my formula returns (after tidying up all your data errors).
    Hi Bob,

    Thank you so much for your time and efforts to help me. Sorry, for the data errors that were not worked before sending the file. May be I'm missing a step, the formula is getting me the last value of coaching observations. The observations I get with this formula are for the last record I'm working. I need the value of the second last record, so that I can recap previous coaching session's observations.

    Thanks again for your time.

    Regards,
    Nagaraj

  8. #8
    The originnal post said last record. If you want second last, try this array formula

    =INDEX('Sheet1 (2)'!$E$1:$E$309,LARGE(IF('Sheet1 (2)'!$A$1:$A$309=C8,ROW('Sheet1 (2)'!$B$1:$B$309)),2))

    It will fail if there is only one.

  9. #9
    Quote Originally Posted by Bob Phillips View Post
    The originnal post said last record. If you want second last, try this array formula

    =INDEX('Sheet1 (2)'!$E$1:$E$309,LARGE(IF('Sheet1 (2)'!$A$1:$A$309=C8,ROW('Sheet1 (2)'!$B$1:$B$309)),2))

    It will fail if there is only one.
    Hi Bob,

    I'm sorry, I'm not able to figure out where I'm missing. I'm not getting the second last value. Can you please share the worked copy of the file you have?

    Many thanks,
    Nagaraj

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •