# Thread: Lookup function to return second last value

1. ## Lookup function to return second last value

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

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. 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. Did you commit Bob's formula with Ctrl+Shift+Enter?

5. Originally Posted by Pecoflyer
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. 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. Originally Posted by Bob Phillips
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.

Regards,
Nagaraj

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. Originally Posted by Bob Phillips
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
•